Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Excel formulae calculation

Posted on 1999-10-12
Medium Priority
218 Views
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,

0
Question by:gordontm
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 4
• 3
• 2
• +3

Expert Comment

ID: 2119967
Dear GordonTm

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
0

LVL 2

Author Comment

ID: 2120178

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.
0

LVL 23

Expert Comment

ID: 2120548
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
0

LVL 4

Expert Comment

ID: 2120812
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
0

LVL 1

Expert Comment

ID: 2121508
Hi

Well, what i'm suggesting you to do is write this line in the begining of the calculation sequence...
Workbooks.Application.Calculation=xlCalculationManual

and then change its value back to...
Workbooks.Application.Calculation=xlCalculationAutomatic
once you finished all the needed changes on your worksheet.

hoped i have helped a little...
bye bye.
0

LVL 23

Expert Comment

ID: 2122016
asaflahv1,

0

LVL 2

Author Comment

ID: 2122474
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.
0

LVL 4

Expert Comment

ID: 2123151
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?
0

LVL 2

Author Comment

ID: 2123323
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
0

LVL 13

Accepted Solution

cri earned 600 total points
ID: 2125926
How about this: Instead placing the 400 formulas in the sheet, set up a sheetchange event reacting to only to changes in the cells which _would_ act on the a.m. functions. Calculate the status in a separate sub and write the result back to the appropriate cell. I use a similar set up to set a calc ID number. If you need more, please ask.
0

LVL 2

Author Comment

ID: 2126128
Thank you - that is a very good idea.  However if I could solve my own problem simply I would prefer that.

0

LVL 4

Expert Comment

ID: 2126265
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.
0

LVL 13

Expert Comment

ID: 2136405
gordontm, thanks for accepting my rather offhand comment as answer, this new feature seems to be really working now.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are â€¦
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calculâ€¦
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increasedâ€¦
###### Suggested Courses
Course of the Month9 days, 10 hours left to enroll