Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

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

excel
MS Office Version: 2016
posted by: ATJBTT (rep: 8) on Fri Apr 12, 2019 at 9:11 pm
Answer
Discuss

Answers

0
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. 
    =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).
posted by: Variatus (rep: 4889) on Sat Apr 13, 2019 at 12:30 am
Discuss
0

Variatus,

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

posted by: ATJBTT (rep: 8) on Sat Apr 13, 2019 at 1:51 pm
Discuss


Answer the Question

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