Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple IF statement
Hi,
I am trying to get a text response within a multiple IFF statement. Basically, if the value in a cell is between 6 and 9 I want the formula to return "drier than normal" Between 10-14, "normal" 15-18 "wetter than normal. This is the equation I have put together, but it keeps coming back "False" =IF(AND(B45<9, B456), "drier than normal", IF(AND(B45<14, B4510),"normal", IF(AND(B4515,B45<18), "wetter than normal"))) Any suggestions? Thanks in advance! Kristen |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple IF statement
First off, you need to add some equal signs to your and < symbols (that
is, make them = and <=); as written, your function will result in an answer of FALSE for values of 6,9,10,14,15 and 18. You might want to consider handling the case when a number is outside of your ranges (add a comma and your message after the "wetter than normal" message). You can also simplify the look of your formula by taking advantage of the fact that the IF statements are executed in order... =IF(B45<6,"Out of Range",IF(B45<10,"Drier than Normal",IF(B45<15,"Normal",IF(B45<19,"Wetter than Normal","Out of Range")))) Here, as I have it structured, the equal signs are not required. Rick "Kristen PS" wrote in message ... Hi, I am trying to get a text response within a multiple IFF statement. Basically, if the value in a cell is between 6 and 9 I want the formula to return "drier than normal" Between 10-14, "normal" 15-18 "wetter than normal. This is the equation I have put together, but it keeps coming back "False" =IF(AND(B45<9, B456), "drier than normal", IF(AND(B45<14, B4510),"normal", IF(AND(B4515,B45<18), "wetter than normal"))) Any suggestions? Thanks in advance! Kristen |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple IF statement
=LOOKUP(B45,{0,6,10,15,19},{"undefined","direr than normal","normal","wetter
than normal","undefined"}) "Kristen PS" wrote: Hi, I am trying to get a text response within a multiple IFF statement. Basically, if the value in a cell is between 6 and 9 I want the formula to return "drier than normal" Between 10-14, "normal" 15-18 "wetter than normal. This is the equation I have put together, but it keeps coming back "False" =IF(AND(B45<9, B456), "drier than normal", IF(AND(B45<14, B4510),"normal", IF(AND(B4515,B45<18), "wetter than normal"))) Any suggestions? Thanks in advance! Kristen |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple IF statement
Maybe this:
=IF(AND(B45<=9, B45=6),"drier than normal",IF(AND(B45<=14, B45=10),"normal",IF(AND(B45=15,B45<=18),"wetter than normal","out-of-range"))) Ranges were tightened to be continuous (there were gaps), with an "out-of-range" added for completeness -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Kristen PS" wrote: I am trying to get a text response within a multiple IF statement. Basically, if the value in a cell is between 6 and 9 I want the formula to return "drier than normal" Between 10-14, "normal" 15-18 "wetter than normal. This is the equation I have put together, but it keeps coming back "False" =IF(AND(B45<9, B456), "drier than normal", IF(AND(B45<14, B4510),"normal", IF(AND(B4515,B45<18), "wetter than normal"))) Any suggestions? Thanks in advance! Kristen |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple IF statement
You've still got gaps, Max. There's a gap between 9 and 10, and another
between 14 and 15. If you are lucky, the value in B45 may have been constrained to be an integer, but the OP didn't say so. -- David Biddulph "Max" wrote in message ... Maybe this: =IF(AND(B45<=9, B45=6),"drier than normal",IF(AND(B45<=14, B45=10),"normal",IF(AND(B45=15,B45<=18),"wetter than normal","out-of-range"))) Ranges were tightened to be continuous (there were gaps), with an "out-of-range" added for completeness -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Kristen PS" wrote: I am trying to get a text response within a multiple IF statement. Basically, if the value in a cell is between 6 and 9 I want the formula to return "drier than normal" Between 10-14, "normal" 15-18 "wetter than normal. This is the equation I have put together, but it keeps coming back "False" =IF(AND(B45<9, B456), "drier than normal", IF(AND(B45<14, B4510),"normal", IF(AND(B4515,B45<18), "wetter than normal"))) Any suggestions? Thanks in advance! Kristen |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple IF statement
Probably subconsciously led-in by the manner in which the OP described it in
the 1st para, David. But you're right that the suggestion could be waterproofed further against this assumption. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... You've still got gaps, Max. There's a gap between 9 and 10, and another between 14 and 15. If you are lucky, the value in B45 may have been constrained to be an integer, but the OP didn't say so. -- David Biddulph |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple IF statement | Excel Discussion (Misc queries) | |||
Help with multiple If statement | Excel Discussion (Misc queries) | |||
Multiple IF Statement | Excel Worksheet Functions | |||
Multiple If Statement | Excel Worksheet Functions | |||
Help with a multiple OR statement. | Excel Discussion (Misc queries) |