gordontm
asked on
Excel formulae calculation
I have a spreadsheet that lists 400 items.
Each item has a set of eight dates which reflect the project lifecycle of the item from requirements signed off, specification signed off through to test sign off and Live date.
I have a function called Status that takes as input these eight dates and another pivot date and returns the current status of the item as per the pivot date.
Now my question is as follows. Each function in the status column is
=Status(Date,I3:Q3)
=Status(Date,I4:Q4)
=Status(Date,I5:Q5)
...
=Status(Date,I400:Q400)
Each time one of the dates is changed then Excel recalculates the entire spreadsheet rather than just the one line.
Is there any way of getting round this?
Calling Date in each function is inefficient so
What I have tried is to store the date in a cell in the spreadsheet and then had the status function thus:
=Status(I3:Q3)
=Status(I4:Q4)
....
=Status(I400:Q400)
and then in the Status function retrieved the Date value from the cell. However this approach was also slow and had other problems associated with it.
Is there any way of storing variables with the spreadsheet?
Thank you,
Each item has a set of eight dates which reflect the project lifecycle of the item from requirements signed off, specification signed off through to test sign off and Live date.
I have a function called Status that takes as input these eight dates and another pivot date and returns the current status of the item as per the pivot date.
Now my question is as follows. Each function in the status column is
=Status(Date,I3:Q3)
=Status(Date,I4:Q4)
=Status(Date,I5:Q5)
...
=Status(Date,I400:Q400)
Each time one of the dates is changed then Excel recalculates the entire spreadsheet rather than just the one line.
Is there any way of getting round this?
Calling Date in each function is inefficient so
What I have tried is to store the date in a cell in the spreadsheet and then had the status function thus:
=Status(I3:Q3)
=Status(I4:Q4)
....
=Status(I400:Q400)
and then in the Status function retrieved the Date value from the cell. However this approach was also slow and had other problems associated with it.
Is there any way of storing variables with the spreadsheet?
Thank you,
ASKER
Thank you for your answer.
In my question I said "then in the Status function retrieved the Date value from the cell" - what you have described is exactly what I did.
However as I said that produced other problems.
I should have said "Are there any *other* ways of storing variables with the spreadsheet" so I apologise for not being clear enough.
I was also actively seeking an answer to the recalculation problem. The reason I mentioned variables is I thought it might be part of the answer.
In my question I said "then in the Status function retrieved the Date value from the cell" - what you have described is exactly what I did.
However as I said that produced other problems.
I should have said "Are there any *other* ways of storing variables with the spreadsheet" so I apologise for not being clear enough.
I was also actively seeking an answer to the recalculation problem. The reason I mentioned variables is I thought it might be part of the answer.
gordontm,
Each time one of the dates is changed then Excel recalculates the entire spreadsheet rather than just the one line.
Is there any way of getting round this?
You can set the workbook to calculate manually instead of automatic. You can do that through code or from Tools, option.
Then you can calculate whatever cell or ranges you need to with the calculate command. ie....... Range("a1").Calculate
more later,
Cheers!
ahammar
Each time one of the dates is changed then Excel recalculates the entire spreadsheet rather than just the one line.
Is there any way of getting round this?
You can set the workbook to calculate manually instead of automatic. You can do that through code or from Tools, option.
Then you can calculate whatever cell or ranges you need to with the calculate command. ie....... Range("a1").Calculate
more later,
Cheers!
ahammar
In your Status() function, you don't have the statement Application.Volatile in it, do you? This would invoke a recalculation of the whole sheet (see http://support.microsoft.com/support/kb/articles/Q81/8/56.ASP for details). Otherwise, it should only recalculate only for those cells where the precedent cells have changed.
Similarly, have you got Application.Calculate or Activesheet.Calculate in your function?
As to your Date thing, addressing the cell from within your function would be a little slower as it would need to go a fetch the value rather than having it there as an argument when you called the function. I can't see why this would cause problems though. There's no quicker way for this that I can think of.
If you fancy it, here's an article on how to optimise the calculations in your worksheet: http://support.microsoft.com/support/kb/articles/Q72/6/22.ASP
Though, looking through it, I don't think it'll help much.
Similarly, here's an article on how Excel approaches the Automatic/Manual calculation thing. It was even a bit of an eye-opener for me: http://support.microsoft.com/support/kb/articles/q161/5/46.asp
Similarly, have you got Application.Calculate or Activesheet.Calculate in your function?
As to your Date thing, addressing the cell from within your function would be a little slower as it would need to go a fetch the value rather than having it there as an argument when you called the function. I can't see why this would cause problems though. There's no quicker way for this that I can think of.
If you fancy it, here's an article on how to optimise the calculations in your worksheet: http://support.microsoft.com/support/kb/articles/Q72/6/22.ASP
Though, looking through it, I don't think it'll help much.
Similarly, here's an article on how Excel approaches the Automatic/Manual calculation thing. It was even a bit of an eye-opener for me: http://support.microsoft.com/support/kb/articles/q161/5/46.asp
Hi
Well, what i'm suggesting you to do is write this line in the begining of the calculation sequence...
Workbooks.Application.Calc
and then change its value back to...
Workbooks.Application.Calc
once you finished all the needed changes on your worksheet.
hoped i have helped a little...
bye bye.
Well, what i'm suggesting you to do is write this line in the begining of the calculation sequence...
Workbooks.Application.Calc
and then change its value back to...
Workbooks.Application.Calc
once you finished all the needed changes on your worksheet.
hoped i have helped a little...
bye bye.
asaflahv1,
Didn't I already say that??
Didn't I already say that??
ASKER
The problem is as Noggy has understood it.
The problem is not with Manual/Automatic recalculation being set. Automatic recalculation is defined as recalculating only those formulae whose input cells have changed and not recalculating all the formulae on the sheet (as happens with me).
I want only one formulae to recalculate each time an input cell is changed.
To answer Noggy - I have not got Application.Volatile or Activesheet.calculate etc. in my function. I will investigate the microsoft support sheets.
The problem is not with Manual/Automatic recalculation being set. Automatic recalculation is defined as recalculating only those formulae whose input cells have changed and not recalculating all the formulae on the sheet (as happens with me).
I want only one formulae to recalculate each time an input cell is changed.
To answer Noggy - I have not got Application.Volatile or Activesheet.calculate etc. in my function. I will investigate the microsoft support sheets.
gordontm - Could you post the code that you are using in your Status() function so that we could see if there may be something else in there which is making Excel think that it is volatile?
ASKER
Here is the code:
=================
'This Function calculates the status of a particular deliverable
'given a Range of eight dates of the Deliverable Lifecycle
'and today's date (or other point of reference)
Function Status(ConsiderDate, RG As Range)
Dim CellValue As Variant
Dim LatestDate As Date
Dim T(8) As String
Dim PostponedFlag As String
'Check Postponed
PostponedFlag = RG(1, 1)
If PostponedFlag <> "" Then
If UCase(PostponedFlag) = "P" Then Status = "Postponed": Exit Function
Status = "Error in Postponed Column": Exit Function
End If
LatestStageDone = 0
LatestDate = 0
'0 = Nothing Done = Awaiting Approval
'1 = One thing done = Requirements Signed Off
'....
'8 = Everything Done = Live
For j = 1 To 8
CellValue = RG(1, j + 1)
If IsDate(CellValue) Then
BoldFlag = RG(1, j + 1).Font.Bold
'If Dates are in wrong order
If LatestDate <> 0 And LatestDate > CellValue Then
'Code to deal with dates
'Not implemented yet
Else
LatestDate = CellValue
End If
If BoldFlag Then 'Date is marked expected
If ConsiderDate < CellValue Then 'Date is in future
'Do Nothing
Else 'Date is in past
Status = "Error: Slippage": Exit Function
End If
Else 'Date is marked done
If ConsiderDate < CellValue Then 'Date is in future
Status = "Error: Dates marked done must be in past"
Exit Function
Else 'Date is in past
LatestStageDone = j
End If
End If
ElseIf UCase(CellValue) = "N" Then
'Do Nothing
ElseIf UCase(CellValue) = "N/A" Then
'Do Nothing
ElseIf UCase(CellValue) = "Y" Then
LatestStageDone = j
ElseIf LTrim(CellValue) <> "" Then
Status = "Error: Invalid Date"
Exit Function
End If
Next j
T(0) = "Awaiting Approval"
T(1) = "Requirements Signed Off"
T(2) = "Specification Complete"
T(3) = "Specification Signed Off"
T(4) = "Development Signed Off"
T(5) = "System Test Signed Off"
T(6) = "UAT Started"
T(7) = "UAT Signed Off"
T(8) = "Live"
Status = T(LatestStageDone)
End Function
=================
'This Function calculates the status of a particular deliverable
'given a Range of eight dates of the Deliverable Lifecycle
'and today's date (or other point of reference)
Function Status(ConsiderDate, RG As Range)
Dim CellValue As Variant
Dim LatestDate As Date
Dim T(8) As String
Dim PostponedFlag As String
'Check Postponed
PostponedFlag = RG(1, 1)
If PostponedFlag <> "" Then
If UCase(PostponedFlag) = "P" Then Status = "Postponed": Exit Function
Status = "Error in Postponed Column": Exit Function
End If
LatestStageDone = 0
LatestDate = 0
'0 = Nothing Done = Awaiting Approval
'1 = One thing done = Requirements Signed Off
'....
'8 = Everything Done = Live
For j = 1 To 8
CellValue = RG(1, j + 1)
If IsDate(CellValue) Then
BoldFlag = RG(1, j + 1).Font.Bold
'If Dates are in wrong order
If LatestDate <> 0 And LatestDate > CellValue Then
'Code to deal with dates
'Not implemented yet
Else
LatestDate = CellValue
End If
If BoldFlag Then 'Date is marked expected
If ConsiderDate < CellValue Then 'Date is in future
'Do Nothing
Else 'Date is in past
Status = "Error: Slippage": Exit Function
End If
Else 'Date is marked done
If ConsiderDate < CellValue Then 'Date is in future
Status = "Error: Dates marked done must be in past"
Exit Function
Else 'Date is in past
LatestStageDone = j
End If
End If
ElseIf UCase(CellValue) = "N" Then
'Do Nothing
ElseIf UCase(CellValue) = "N/A" Then
'Do Nothing
ElseIf UCase(CellValue) = "Y" Then
LatestStageDone = j
ElseIf LTrim(CellValue) <> "" Then
Status = "Error: Invalid Date"
Exit Function
End If
Next j
T(0) = "Awaiting Approval"
T(1) = "Requirements Signed Off"
T(2) = "Specification Complete"
T(3) = "Specification Signed Off"
T(4) = "Development Signed Off"
T(5) = "System Test Signed Off"
T(6) = "UAT Started"
T(7) = "UAT Signed Off"
T(8) = "Live"
Status = T(LatestStageDone)
End Function
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you - that is a very good idea. However if I could solve my own problem simply I would prefer that.
From your code, I can't see why Excel would think that it is a Volatile calculation. However, I have not tested it yet. I'll let you know later.
gordontm, thanks for accepting my rather offhand comment as answer, this new feature seems to be really working now.
Regarding your last question to store variables within the spreadsheet. You can do this by using the Name funtion. Either through the menu or VBA.
Through the menus goto Insert, Name, Define... Now add a name and under RefersTo you can add the variable you would like to store. When you save and reopen the worksheet the variable will be stored in the name.
In VBA this can be achieve by:
ActiveWorkbook.Names.Add Name:="test", RefersToR1C1:="=" & Format(Now(), "d-mmm-yy")
Regards
Sean