Problem with nested 'if statements' and successions.

Question

Level 1

2 points

Problem with nested 'if statements' and successions.

Hi folks 🙂

My first "to the point" question here was solved magically by a member from here. I got shocked because all the stuff I was able to do with that thing 😀 😀 😀

But you know, we're curious so straight to the point: I don't know anything about mathematics or programation. Anything. But I have nice ideas, at last to me! 😝 and I need HeLp!!! 😁 hEeEeLP! (that's the right one!) Okay... So... I need a cell to rest values on other cell. Oh, by the way, English is my second languaje. I don't have a third one, so I think is the last one. Anyway.

Let me put it clear 😠 (that's me with a serious face):

For example, I have Cell A and Cell B.

Cell A = 31

Cell B = 82

I need, when each time I decrease one cell, I need both cells to increase.

Cell A = 32

Cell B = 84

Note that Cell B increases in 2 steps. That's deliverated.

I don't know how to code this in Numbers; nor I don't know mathematics, remember that.

I tried this:

=IF(B3=30,80,IF(B3>30,82,IF(B3<30,79)))

and it works. But the problem is that I should write each corrsponding step and that's like it must be another way, don't know, something like 'from' 'to' or the like.

Please, any help will be really appreciated!

Ø3 🙂

MacBook Pro, Mac OS X (10.7.4), 8 GB RAM ATI 7770 HD 1 GB DDR5

Posted on Sep 11, 2012 1:46 PM

Reply

Answer 1

Best reply

Barry

Level 9

59,861 points

Sep 11, 2012 4:27 PM in response to Quantum3

Hi 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

Reply

Answer 2

Quantum3 Author

Level 1

2 points

Sep 12, 2012 8:54 AM in response to Barry

Oh 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 🙂

Reply