Increasing one cells value based on incremental increases in another

**AndyPandy** · 06-19-2013, 06:25 AM

Hello,

I am trying to figure out how I can increase the value of one cell based on the value of another (but only if this valus is above a certain threshold). An example would be:

Cell A has a value of 150, which means that any values below this will not increase the value of the target cell.
Cell B has a value of 160, which is a 1-time increment of 10 from the value of cell A. If cell B had a value of 159, there would be no 1-time increment.
Cell C should increase by one each time cell B increases by 10 over the value 150 in A.

So if B is 159, C is 0, if B is 162, C is 1, if B is 174, C is 3. How do i go about making formulas for this? And how would I do it if I wanted negative numbers for incremental decreases below 150? E.g. if B was 139, C would be -1.

I hope this made sense. It's kind of hard to explain by writing, and due to the material being work sensitive, I can't really post any examples.

Please let me know if what I wrote is not understandable.

Andy

**Chris 53** · 06-19-2013, 06:34 AM

Try
=ROUNDDOWN((B1-A1)/10,0)

**Pete_UK** · 06-19-2013, 06:38 AM

Try this in C1:

=INT(ABS((B1-A1))/10)*SIGN(B1-A1)

Hope this helps.

Pete

**AndyPandy** · 06-19-2013, 08:11 AM

Thanks guys, both formulas worked like a charm!

Is the same thing possible to do if you're working with an interval instead of just one specific threshold? Say for example that I would like to decrease C if B is under the limit of 70, and increase C if B is over 150 (as in the OP). So the interval would be 70-150, and no change in C would happen inside this.

**Chris 53** · 06-19-2013, 08:41 AM

Try
=IF(C1<A1,ROUNDDOWN((C1-A1)/10,0),IF(C1>B1,ROUNDDOWN((C1-B1)/10,0),0))

Where A1 = lower limit, B1 = upper limit. That will allow you to change the lower and upper limits as and when you want to. If you aren't going to change the lower and upper limits you can amend the formula to replace A1 with 70 and B1 with 100.

Hope this helps.

Chris