IF statement - multiple scenarios

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Laz - 10 Oct 2007 19:52 GMT

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 larryl54@yahoo.com
Thanks a lot.
Larry (Laz)

Reply to this Message

JNW - 10 Oct 2007 20:19 GMT

=if(and(a1>0,a1<13),1,if(and(a1<0,a1>-13),-1,if(and(a1>12,a1<25),2,if(and(a1<-12,a1>-25),-2,if(a1>24,3,if(a1<-24,-3,0))))))

This assumes that if a1 is 0 then the result is 0 also.

Signature

JNW

> 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.
[quoted text clipped - 9 lines]
> Thanks a lot.
> Larry (Laz)

Reply to this Message

Roger Govier - 10 Oct 2007 20:26 GMT

Hi

One way
=SIGN(A1)*IF(ABS(A1)<13,1,IF(ABS(A1)<25,2,3))

Signature

Regards
Roger Govier

> I'm sure this will be simple to many of you but I really need to know how
> to
[quoted text clipped - 10 lines]
> Thanks a lot.
> Larry (Laz)

Reply to this Message

JNW - 11 Oct 2007 02:53 GMT

Much more concise than mine! I didn't know about the sign function. Thanks.

Signature

JNW

> Hi
>
[quoted text clipped - 15 lines]
> > Thanks a lot.
> > Larry (Laz)

Reply to this Message

Rick Rothstein (MVP - VB) - 11 Oct 2007 04:45 GMT

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

> Much more concise than mine! I didn't know about the sign function.
> Thanks.
[quoted text clipped - 19 lines]
>> > Thanks a lot.
>> > Larry (Laz)

Reply to this Message