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.

[TABLE="width: 153"]
<tbody>[TR]
[TD]Level[/TD]
[TD]Expected ID[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]
It should not do the following:
[TABLE="width: 153"]
<tbody>[TR]
[TD]Level[/TD]
[TD]Expected ID[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]

Any help will be greatly appreciated![/TD]
[TD="align: right"][/TD]
[/TR]
</tbody><colgroup><col><col></colgroup>[/TABLE]
[/TD]
[TD="align: right"][/TD]
[/TR]
</tbody><colgroup><col><col></colgroup>[/TABLE]

 

[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