IF statement

[Johneeo]

I am familiar with IF statements, however, the limitation of having
"thens" has me stumped.

The situation is as follows. I am doing a sales commissio
spreadsheet. If the sales person hits the $20 sale, they get standar
bonus, i.e. 100%. However, if they hit $25, they get 5% increase. Th
sale targets increase by $5, where $30 sale gets a 10% increase, a $3
sale gets a 15% increase, and on and on.

Is there a way to write an IF statement beyond the 7 "thens" that I a
able to do

 

[John P]

J

Checkout lookup in help.
Come back if you need more help!
Cheers

John

 

[Jason Morin]

Typically you use a lookup table in these situations, or
just build it into your formula:

=LOOKUP(E1,{20,25,30,35},{1,1.05,1.1,1.15})

where E1 = the sale.

However, in your situation, you could also use:

=FLOOR(E1+80,5)/100

HTH
Jason
Atlanta, GA

 

[Frank Kabel]

Hi John
though LOOKUP should work I would recommend using
VLOOKUP

 

[Don Guillett]

See if this gets you .15 from 35 entered in cell a2
=IF(A2>20,(INT((A2-20)/5)*0.05),0)

 

[Don Guillett]

Jason,

Below 20?

 

[Jason Morin]

Below 20? I read it as no sales commission in that case.
Maybe some training for the salesman <g>. The lookup
formula would still work, but the second would still
calculate a commission for under $20 sale...I guess the
OP has his choice of formulas.

 

[Don Guillett]

For $15, the first one gives n/a, second .95

 

[Johneeo]

Typically you use a lookup table in these situations, or
just build it into your formula:

=LOOKUP(E1,{20,25,30,35},{1,1.05,1.1,1.15})

where E1 = the sale.
<<<<<<<<

That worked great, thanx

 

[Johneeo]

Jason,

Below 20?
<<<<<

The factor becomes .75 at $15, .50 at $10, and .25 at $5.

But thanx to everyone for the answer, This is my first time here, an
it seems like a great forum.

I was playing with this stinkin thing for over an hour yesterday, an
figured out I needed to find an Excel forum, glad I did.: