If statement

[Kevin Vidrine]

Can I put multiple if's in the same cell. This is what Im
trying to say.
If(b1=3,B13*0.1,0) If B1 =3 the multiply B13 by .1
otherwise return 0.
If(b1=4,b13*0.15,0)
If(B1=5,B13*0.16,0)
If(B1=6,B13*0.17,0)
If(B1=7,B13*0.18,0)
If(B1=8,B13*0.19,0)
If(B1=>9,B13*0.20,0)

I want to make all these into one formula so it return
the correct value no matter what number I put in b1.
Thanks

 

[RagDyer]

I just wanted to see if I could do it without using a continuous IF
statement.
It's still pretty long, but it was, (for me), a mental exercise!

=IF(B1>=9,B13*0.2,IF(OR(B1<>{3,4,5,6,7,8}),0,B13*(CHOOSE(B1,0,0,0.1,0.15,0.1
6,0.17,0.18,0.19))))

I'm sure someone will come up with a much shorter one.


HTH,

RD
========================================
Please keep all correspondence within the Group, so all may benefit!
========================================

 

[ledu]

Kevin,

=IF(B1=3,B13*.01,IF(B1=4,B13*.15,IF(B1=5,B13*.16,IF
(B1=6,B13*.17,IF(B1=7,B13*.18,IF(B1=8,B13*.19,IF
(B1=9,B13*.2,0)))))))

This formula uses 7 nested IFs. Instead of typing 0 after
B13*.01 just start another IF statement. Just keep track
of how many )) you need at the end. If that is the only
thing wrong with your formula Excel will suggest the
correct formula.

Ledu

 

[Harlan Grove]

=IF(B1=3,B13*.01,IF(B1=4,B13*.15,IF(B1=5,B13*.16,IF
(B1=6,B13*.17,IF(B1=7,B13*.18,IF(B1=8,B13*.19,IF
(B1=9,B13*.2,0)))))))

This formula uses 7 nested IFs. Instead of typing 0 after
B13*.01 just start another IF statement. Just keep track
of how many )) you need at the end. If that is the only
thing wrong with your formula Excel will suggest the
correct formula.

There are better ways to do this. If B13 is always a number,

=B13*VLOOKUP(B1,
{-9E307,3,4,5,6,7,8,9;0,0.1,0.15,0.16,0.17,0.18,0.19,0.2},2,0)

Better still to put the constant values into a range as a lookup table.