Numbers '08 "If" with a greater or less than answer

Question

Level 1

4 points

Numbers '08 "If" with a greater or less than answer

Hi all - I have a cell with a sum D & E28 (merged cell) that I want another cell B30 to display a value based on what is in cell D & E28.

If cell D & E28 is 8 or less I want B30 to say 8. If D & E28 is greater than 8.001 but less than 12 I want B30 to say 12 and if D & E28 is greater than 12.001 but less then 18 I want B30 to say 18
Any ideas?

Thanks
Peter

Mac Mini, Mac OS X (10.4.10)

Posted on Apr 21, 2008 5:23 AM

Reply

Answer 1

Best reply

Jerrold Green1

Level 7

30,211 points

Dec 21, 2010 2:58 PM in response to pdmjr

pdmjr,

=IF(ROUND(D28,3)<=8.001, 8, IF(ROUND(D28,3)<=12.001, 12, IF(ROUND(D28,3)<=18, 18, "Over 18")))

should do it for you.

The nested IF statements handle your logic. The ROUND statements handle internal math errors and your need to break at .001. You didn't say what to do with inputs of 18 or greater, so I suggested a text string for that case.

The form of the IF statement is: If (logical statement) is True, Then do this, Else do this. Your case requires three tests. You could quibble about a value that is, say, 12.0011 and rounds to 12.001, but I doubt you are concerned about that level of detail. If so let me know.

Does this help?

Regards,

Jerry

Message was edited by: Jerrold Green1

Reply

Answer 2

WWJD

Level 6

15,830 points

Dec 21, 2010 2:58 PM in response to pdmjr

You might also try a lookup table. An example was given a few days ago. The benefit of a lookup table is when the range values change, the equations do not have to change.

Regards,

Reply

Answer 3

pdmjr Author

Level 1

4 points

Dec 21, 2010 2:58 PM in response to WWJD

Hi thanks a bunch! I tried both solutions and they both worked. I am a little more familiar with the look up table as I have many all over the spreadsheet but I like the round up one also!

Thanks again to both of you!
Peter

Reply