Dealing with Negative Numbers in Nested If

[JohnMarcus]

Hello! I really need help on this problem. I don't know how to manipulate the formula with quoting the "You can't have cups less than zero". I really need help on this, please....please... I hope somebody can help me. thank you. God bless.


Teletubbies coffee - Nested If

Create a nested If function to describe the Teletubbies coffee drinking habits based on the following criteria:

• 0 cups = Tea drinker

• 1-5 cups = Normal

• More than 5 cups = Caffeine fiend

Copy the function down and check that it works.

Think carefully about how to structure the If function



Try modifying the If function so that if Cups of coffee is a negative number you see an appropriate error message.

Make sure the rest of the function still works!

 

[ChrisR]

=IF(NoOfCupsOfCoffee<0,"You Can't Have Less than Zero Cups!",If(AND(NoOfCupsOfCoffee>=0,NoOfCupsOfCoffee<3),"Tea Drinker",If(AND(NoOfCupsOfCoffee>=3,NoOfCupsOfCoffee<6),"Normal",If(NoOfCupsOfCoffee>=6,"Caffiene Fiend")

Just replace NoOfCupsOfCoffee with your cell reference for the number of cups

 

[JohnMarcus]

The word "You can't have less than 0 cups" should not be quoted. The formula should be manipulated to get that error message. By the way, thanks for your answer!

 

[Aladin Akyurek]

Not very clear... Looks like some kind of assignment. I'd try:

Either...

=LOOKUP(B2,{-9.99E+307,0,1,6},
    {"You can't have less than 0 cups!","Tea drinker","Normal","Caffeine Fiend"})

Or...

=IF(ISNUMBER(B2),
   IF(B2>=0,
     LOOKUP(B2,{0,1,6},{"Tea drinker","Normal","Caffeine Fiend"}),
     "You can't have less than 0 cups!"),
   "")

By the way, what is the nested if formula you seem to have?

 

[JohnMarcus]

Thank you for the effort and time. But the formula should not contain a quoted "You can't have less than 0 cups". The formula should be modify to get an error which is You can't have less than 0 cups. By the way my formula is =IF(B3<1,"Tea Drinker",IF(B3<5,"Normal","Caffeine fiend")). Please just make some modifications on it. thank you. I will wait for your reply.

 

[Aladin Akyurek]

[...] the formula should not contain a quoted "You can't have less than 0 cups". The formula should be modify to get an error which is You can't have less than 0 cups.[...]

The exhibit you posted displays the message as such, so it's hard to make sense of the above other than what has been already proposed....