Please help with a quick IF Function formula adjustment

[smarch]

The current formula: B2 is a given number. I enter C2 and the below formula calculates D2 based on the following: For every 2 units higher that C2 is than B2, D2 will go up 1. For every 3 units below that C2 is than B2, D2 will go down 1. Below is that formula that is entered into cell D2:

=IF(C2="","",IF((C2-B2)=0,B2,IF((C2-B2)>0,B2+IF((C2-B2)>0,CHOOSE(C2-B2,0,1,1,2,2,3,3,4,4,5,5,6,6,7,7,8,8,9,9,10,10,11,11,12,12),B2),B2+CHOOSE((C2-B2)*-1,0,0,-1,-1,-1,-2,-2,-2,-3,-3,-3,-4,-4,-4,-5,-5,-5,-6,-6,-6,-7,-7,-7,-8,-8))))

Desired formula: If C2 is 2 units or more higher than B2, then D2 will go up 1. If C2 is 3 units or more lower than B2, then D2 will go down 1.

Someone built the above formula for me and I cannot figure out how to adjust it to meet the desired result. All help is appreciated.


Thanks!

 

[AkaTrouble]

you cannot increase or decrease a cell value with a formula in that cell (without using VBA)

you can have a helper cell(s) that you can calculate values then have a sum of that.

to create your desired results of +1 / -1 / 0

this formula will return that for you but the running total is different issue we would need more info

=IF((C2-B2)>2,1,IF((C2-B2)<-2,-1,0))

i may of misunderstood your question though if so sorry

 

[smarch]

Thanks for your response. It was probably me who is not clear. I apologize. Let me try again:

Currently, B2 is a given number. When I enter a number equal to, or 1 greater than B2 into cell C2, then the formula (located in D2) returns the same value as B2. If I enter a number into C2 which is 2 or 3 units higher than B2, then the formula adds 1 to the original B2 value. This continues all the way up in multiples of 2. When I enter a number 1 or 2 less than B2 into cell C2, then the formula (located in D2) returns the same value as B2. If I enter a number into C2 which is 3, 4 or 5 units less than B2, then the formula adds 1 to the original B2 value. This continues all the way down in multiples of 3.

What I would like the formula to do is essentially the exact same thing, but different units measurments. Here is what I would like the formula to do:

When I enter a number equal to, or 1 greater than B2 into cell C2, then the formula (located in D2) returns the same value as B2. If I enter a number into C2 which is 2 or more than B2, then the formula adds 1 to the original B2 value. When I enter a number 1 or 2 less than B2 into cell C2, then the formula (located in D2) returns the same value as B2. If I enter a number into C2 which is -3 or less than B2, then the formula subtracts 1 from the original B2 value.

Does this make sense?

 

[AkaTrouble]

think i got it this should do that

=IF((C2-B2)>1,B2+1,IF((C2-B2)<-2,B2-1,B2))

 

[smarch]

PERFECT!!

Thanks!

 

[smarch]

Is there any way to have the calculation in D2 not show up until something is in C2? Right now, when I enter the formula, D2 automatically calcs one less because nothing is entered. In the previous formula, D2 would be blank until a value was entered into C2.

Thanks!

 

[danzon]

=if(c2="","",if((c2-b2)>1,b2+1,if((c2-b2)<-2,b2-1,b2)))

 

[AkaTrouble]

try this

=IF(C2="","",IF((C2-B2)>1,B2+1,IF((C2-B2)<-2,B2-1,B2)))

 

[smarch]

Thanks!