
Barry Sep 11, 2012 4:27 PM
Re: Problem with nested 'if statements' and successions. in response to Quantum3Hi Q,
You wrote: =IF(B3=30,80,IF(B3>30,82,IF(B3<30,79)))
The formula allows for three possibilities: B3 is less than 30, B3 is 30 or B3 is greater than 30.
The formula, as written, could be simplified by dropping one of the conditions. It doesn't matter which one, as one and only one must be true for any possible value of B3.
Any one of these three formulas could be used:
=IF(B3=30,80,IF(B3>30,82,79))
=IF(B3<30,79,IF(B3>30,82,80))
=IF(B3>30,82,IF(B3<30,79,80))
But I infer from your post that you want to deal with more than three discrete values of B3, and to get a distinct result for each possible B3 value.
Let's look at your statement, "Cell B increases in 2 steps."
I take this to mean that the value in the cell containing the formula should increase by 2 for each increase of 1 in the value in B3." The problem with this interpretation is that it does not fit with the "79" value you expect for B3=29. I'm assuming 79 is an error, and the result for B3=29 should be 78, two less that the 80 result for B3=30.
If that assumption is correct, then the mathematical description of the relationship between the two values is:
y = 2x + k
Where x is the value in B3, k is a constant, and y is the result.
We know from your formula that for x=30, y=80.
Substituting those two values in the equation, we get:
80 = 2x30 + k
80 = 60 + k
From which we can determine that k = 20.
The formula is the right side of the equation above (with the now known value of k); the result is the value returned to the cell containing the formula:
C3: =2*B3 + 20
I've placed the formula in cell C3, but it can be placed anywhere on the table.
Regards,
Barry

Quantum3 Sep 12, 2012 8:54 AM
Re: Problem with nested 'if statements' and successions. in response to BarryOh Barry, thanks so much for your time and your ability to understand my problem (and my poor english). Thank you very much, noble person!
Ø3