Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Can someone fix this IF(AND) for me?
=IF(B2="HOURLY",1.5*C2,IF(B2="PT HOURLY",C2,IF(AND(B2="FT",C2<100000,c2*2,IF(AND(B2 ="FT",C2100000,C2<=1250000),C2*2.25,IF(AND(B2="FT ",C2125000,C2<175000),C2*2.4,IF(AND(B2="FT",C217 5000),C2*2.5))))))
what im trying to do is if B2=HOURLY then 1.5*C2 and if B2=PT HOURLY then 1*C2 and if B2=FT and C2<100000 then 2*C2 and if B2=FT and C2<100000 and =125000 then C2*2.25 and if B2=FT and C2<125000 and =175000 then C2*2.4 and if B2=FT and C2175000 then C2*2.5 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can someone fix this IF(AND) for me?
"gusjen" wrote:
=IF(B2="HOURLY",1.5*C2,IF(B2="PT HOURLY",C2, IF(AND(B2="FT",C2<100000,c2*2, IF(AND(B2="FT",C2100000,C2<=1250000),C2*2.25, IF(AND(B2="FT",C2125000,C2<175000),C2*2.4, IF(AND(B2="FT",C2175000),C2*2.5)))))) Your problem might simply be a misplaced right-parenthesis, namely: IF(AND(B2="FT",C2<100000),c2*2, But it is hard to say for sure, since you do not explain what the problem is. Perhaps the misplaced right-parenthesis is just in your posting. You certainly have some other typos; for example, 1250000 should be 125000. "gusjen" wrote: what im trying to do is if B2=HOURLY then 1.5*C2 and if B2=PT HOURLY then 1*C2 and if B2=FT and C2<100000 then 2*C2 and if B2=FT and C2<100000 and =125000 then C2*2.25 and if B2=FT and C2<125000 and =175000 then C2*2.4 and if B2=FT and C2175000 then C2*2.5 This can be simplified considerably. But what if B2 is not "HOURLY", "PT HOURLY" or "FT"? Perhaps: =IF(B2="HOURLY", 1.5*C2, IF(B2="PT HOURLY", C2, IF(B2="FT", C2*LOOKUP(C2,{0,100000,125000,175000},{2,2.25,2.4, 2.5}), ""))) Be mindful of the curly braces. It is best if you copy the formula from this response and paste into Excel. That returns the null-string ("") if B2 is none of the 3 strings. And that assumes that C2 is never negative. If it might, change the first 0 in LOOKUP to something like -1E300, an arbitrary "large" negative value (in magnitude). |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|