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


Excel formulae calculation

Posted on 1999-10-12
Medium Priority
Last Modified: 2008-03-17
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

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:
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,

Question by:gordontm
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
  • Learn & ask questions
  • 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")



Author Comment

ID: 2120178
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.
LVL 23

Expert Comment

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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.


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 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:
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:

Expert Comment

ID: 2121508

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

and then change its value back to...
once you finished all the needed changes on your worksheet.

hoped i have helped a little...
bye bye.
LVL 23

Expert Comment

ID: 2122016
Didn't I already say that??


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.

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?

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

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.


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

Featured Post

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

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…

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question