|
PREVIOUS Function
PREVIOUS returns the value of the cell at the previous calculation.
PREVIOUS allows you to get the value from the previous calculation for the cell containing the PREVIOUS function.
Normally if you create a formula that refers to the cell that contains the formula, Excel detects a circular reference that requires using iteration to solve.
PREVIOUS does NOT cause a circular calculation.
You can use PREVIOUS as a parameter of a VBA UDF. This is useful when you have a slow UDF that you want to be able to short-circuit under certain conditions. For example, if your UDF retrieves data from a remote server you may only want to retrieve a new value when the server has been refreshed.
PREVIOUS is NOT thread-safe, is optionally volatile and does not handle arrays.
Previous Syntax
PREVIOUS (Volatile)
Volatile
Optional (default True). True causes the function to be calculated at every recalculation.
Do not use False for this argument when the formula containing the function uses or refers to a cell containing a volatile function.
Remarks and Limits
PREVIOUS is NOT a thread-safe function, and so any formula containing PREVIOUS will not take advantage of Excel’s multi-threaded calculation.
Do not use False for the Volatile argument when the formula containing the function uses or refers to a cell containing a volatile function.
When a formula containing PREVIOUS is entered, the value from the previous recalculation will be reset.
Examples
=MyUDF(Refresh,theInput,PREVIOUS(FALSE))
If MyUDF is a VBA function that retrieves data from a remote server based on the parameters in theInput, you could program it to only get the data from the remote server when the Refresh switch was true, otherwise it could return the value from the last calculation.
=IF(A1>PREVIOUS(),A1,PREVIOUS())
Gives the largest value that cell A1 has ever contained.
=PREVIOUS(FALSE)+A1
Gives a cumulative sum of all the numbers that have changed in A1.
Note that if the same number is entered twice in succession in cell A1 this formula will NOT accumulate the same number twice.
=IF(COUNTA(C1)>0,PREVIOUS(FALSE)+1,0)
Counts the number of times the contents of cell C1 has been changed, reset to zero if C1 is cleared or the contents deleted.
Note that if the same value is entered twice in succession in cell A1 this formula will NOT increment the change count.
|
