Increment the Cell Values using a Formula that Ignore Certain Values

0

I have a formula in column C that returns a value of 1, if the corresponding cell in column F is greater than 1,000.  If it's less than 1,000, it returns a value of -1.

I need some help with a formula in column D where I would like to increment its corresponding cell's value by 1, if column C's value is 1.  I can't figure out how to ignore the cells that equals -1 and go up the last cell in column C that equals 1 and increment based on that value.

Example, in D7, I would like my formula to ignore C6 since it's -1, and pick-up the 488 value in D5 and return 489 in D7.

Thank you

0

Two things to start.

1. Your formula in column C is imprecise because it doesn't provide for the case that F = 1000. You may like [C2] = IF(\$F2<1000,-1,1). As an alternative consider using >= or <=.  The formula below would assign 0 if F = 1000.
=IF(\$F2=1000,0,IF(\$F2<1000,-1,1))
The same effect can be produced using the SIGN() function.
=SIGN(\$F2-1000)
That would work faster because it's simpler.
2. By making the count in column D dependent upon the formula in column C you introduce a potential source of error. Consider using the same logic for the count as you apply to the classification in column C.
= \$D1 + IF(\$F2>=1000,1,0)
Observe that this formula would add 1 even if F2 has the exact value of 1000 (or greater).
0

Variatus,

Thank you for your help and suggestions.  This is exactually what I needed.