Reset incremental counter based on value in adjacent column

[Arline]

Hi there! I would really appreciate your help with creating a formulae which will achieve the expected IDs below. Each time Level = 1, the counter for Expected ID should reset to 0 and start to increment by 1 again.

Level	Expected ID
1	0
2	1
2	2
2	3
1	0
2	1
1	0
1	0
2	1
2	2
2	3
2	4
It should not do the following: Level Expected ID 1 1 2 1 2 2 2 3 1 0 2 4 1 0 1 0 2 5 2 6 2 7 2 8 Any help will be greatly appreciated! <tbody> </tbody><colgroup><col><col></colgroup>

<tbody>
</tbody><colgroup><col><col></colgroup>

 

[AndyPandy27]

Hi Arline,

Assuming your Levels are in Column A starting in cell A1, and your Expected IDs are in Column B:

In Cell B1: =IF(A1=1,0,"What you want if the first Level is not 1") - change the bit in red to whatever should appear if the first Level is not 1.
In Cell B2: =IF(A2=1,0,B1+1)

Then copy down the formula in cell B2 as far as you need.

If the first Level in Column A is always going to be 0, therefore the first Expected ID in Column B should always be 0, just enter a 0 in B1, and use the same formula in B2 to copy down.

Good luck,

AP

 

[Arline]

Thank you very much AndyPandy! Worked like a charm