Excel formula to get a volatile increment value

Question

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

You could turn on iterative calculation, set the maximum number of iterations to 1 and then just enter `=A1+1` in A1. — Rory, Aug 18 '16 at 07:39
Could you post those steps into an answer @Rory? Just to make sure, its output must behave like a volatile function. — Anastasiya-Romanova 秀, Aug 18 '16 at 07:46
Try it and see. If it does what you need, I'll put it in as an answer. ;) — Rory, Aug 18 '16 at 07:49
Geez, are you being serious @pnuts? What kind of person are you? — Anastasiya-Romanova 秀, Aug 18 '16 at 07:51
@pnuts Did you just downvote [this question of mine](http://stackoverflow.com/q/38571256/3397819)? If so, could you tell me the reason? — Anastasiya-Romanova 秀, Aug 18 '16 at 07:54
@Rory I tried your advice and it works. Many thanks. Could you please write an answer to this question? I'll accept it :) — Anastasiya-Romanova 秀, Aug 19 '16 at 03:02

score 1 · Accepted Answer · answered Aug 19 '16 at 07:20

1

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

answered Aug 19 '16 at 07:20

Rory

32,730
5
32
35

Excel formula to get a volatile increment value

1 Answers1