Search code examples
excelexcel-formulaexcel-2010excel-2007formulas

Excel formula to get a volatile increment value

Could someone here provide me a non-VBA method to obtain a formula where its output will increase by 1 whenever calculation occurs in any cells on the worksheet? The formula is allowed to use any cells on the worksheet.

The best I can get to perform such a thing is to use the following volatile UDF:

Public Num As Long

Public Function INC() As Long
    Application.Volatile
    Num = Num + 1
    INC = Num
End Function
Solution

  • You can do this fairly simply with iterative calculation. You could turn on iterative calculation in the Formulas section of Options, set the maximum number of iterations to 1 and then just enter =A1+1 in A1