Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF statement - multiple scenarios
I'm sure this will be simple to many of you but I really need to know how to
formulate this into one statement, Any help is greatly appreciated. IF A1 0 but < 13, 1 IF A1 < 0 but -13, -1 IF A1 12 but < 25, 2 IF A1 < -12 but -25, -2 IF A1 24, 3 IF A1 < -24, -3 You can also email me at Thanks a lot. Larry (Laz) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF statement - multiple scenarios
=if(and(a10,a1<13),1,if(and(a1<0,a1-13),-1,if(and(a112,a1<25),2,if(and(a1<-12,a1-25),-2,if(a124,3,if(a1<-24,-3,0))))))
This assumes that if a1 is 0 then the result is 0 also. -- JNW "Laz" wrote: I'm sure this will be simple to many of you but I really need to know how to formulate this into one statement, Any help is greatly appreciated. IF A1 0 but < 13, 1 IF A1 < 0 but -13, -1 IF A1 12 but < 25, 2 IF A1 < -12 but -25, -2 IF A1 24, 3 IF A1 < -24, -3 You can also email me at Thanks a lot. Larry (Laz) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF statement - multiple scenarios
Hi
One way =SIGN(A1)*IF(ABS(A1)<13,1,IF(ABS(A1)<25,2,3)) -- Regards Roger Govier "Laz" wrote in message ... I'm sure this will be simple to many of you but I really need to know how to formulate this into one statement, Any help is greatly appreciated. IF A1 0 but < 13, 1 IF A1 < 0 but -13, -1 IF A1 12 but < 25, 2 IF A1 < -12 but -25, -2 IF A1 24, 3 IF A1 < -24, -3 You can also email me at Thanks a lot. Larry (Laz) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF statement - multiple scenarios
Much more concise than mine! I didn't know about the sign function. Thanks.
-- JNW "Roger Govier" wrote: Hi One way =SIGN(A1)*IF(ABS(A1)<13,1,IF(ABS(A1)<25,2,3)) -- Regards Roger Govier "Laz" wrote in message ... I'm sure this will be simple to many of you but I really need to know how to formulate this into one statement, Any help is greatly appreciated. IF A1 0 but < 13, 1 IF A1 < 0 but -13, -1 IF A1 12 but < 25, 2 IF A1 < -12 but -25, -2 IF A1 24, 3 IF A1 < -24, -3 You can also email me at Thanks a lot. Larry (Laz) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF statement - multiple scenarios
If the values will always fall within a range of -37<=value<=37, then we can
get even more concise... =SIGN(A1)+FLOOR(A1/12.5,SIGN(A1)) If the values can fall outside of that range, we can still come in with a formula that is 2 characters more concise... =SIGN(A1)*(1+FLOOR(MIN(ABS(A1),36)/12.5,1)) Rick "JNW" wrote in message ... Much more concise than mine! I didn't know about the sign function. Thanks. -- JNW "Roger Govier" wrote: Hi One way =SIGN(A1)*IF(ABS(A1)<13,1,IF(ABS(A1)<25,2,3)) -- Regards Roger Govier "Laz" wrote in message ... I'm sure this will be simple to many of you but I really need to know how to formulate this into one statement, Any help is greatly appreciated. IF A1 0 but < 13, 1 IF A1 < 0 but -13, -1 IF A1 12 but < 25, 2 IF A1 < -12 but -25, -2 IF A1 24, 3 IF A1 < -24, -3 You can also email me at Thanks a lot. Larry (Laz) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF statement with multiple scenarios. | Excel Discussion (Misc queries) | |||
How to do if/then formula with multiple scenarios. | Excel Discussion (Misc queries) | |||
consolidating results of multiple scenarios | Excel Discussion (Misc queries) | |||
Return multiple scenarios | Excel Worksheet Functions | |||
Multiple vendors and scenarios - Excel | Excel Worksheet Functions |