Increment the Cell Values using a Formula that Ignore Certain Values


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



Selected Answer

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. 
    The same effect can be produced using the SIGN() function. 
    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).


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


Answer the Question

You must create an account to use the forum. Create an Account or Login