Sliding Scale for Bonuses

[KeithS.]

I'm using Excel 2010.
I'm working on sales commissions & bonuses. The commission part is easy – just multiply the cell w/ the sales number times the commission rate (as an absolute reference).

I'm stuck on our sliding scale for bonuses.
For example:

• IF sales >=120,000, bonus =2.5%
• IF sales >=150,000, bonus =2.75%
• IF sales >=200,000, bonus =3%

How do I write the IF formula for multiple arguments?

How it should read:
IF sales >=120,000, multiply the cell w/ the sales amount times 2.5%. IF sales >=150,000, multiply the cell w/ the sales amount times 2.75%. IF sales >=200,000, multiply the cell w/ the sales amount times 3%.
I'm thinking it should start with =IF(OR( but I'm nt getting anywhere with that approach.

I'm pretty new to Excel, so trying to avoid VBA solutions or macros.

Many thanks for your help!

 

[TKB]

You want to use the "OR" function.

Check out How to use nested IF statements in Excel with AND, OR, NOT | Experiments in Finance

 

[Marcelo Branco]

Assuming Sales in A2 try this

=A2*LOOKUP(A2,{0;120000;150000;200000},{0;0.025;0.0275;0.03})

M.

 

[KeithS.]

Thanks! Thank link had exactly what I needed!

 

[KeithS.]

Thanks for the LOOKUP option - I'll work with it.
For some reason it gave the same result as
=IF(E7>=120000, E7*2.5%, IF(E7>=150000, E7*2.75%, IF(E7>=200000, E7*3%, "")))
for my first test number, but when I copied the formula down (using the fill handle) all the following figures were different.

 

[Marcelo Branco]

Thanks for the LOOKUP option - I'll work with it.
For some reason it gave the same result as
=IF(E7>=120000, E7*2.5%, IF(E7>=150000, E7*2.75%, IF(E7>=200000, E7*3%, "")))
for my first test number, but when I copied the formula down (using the fill handle) all the following figures were different.

It's because the first condition of your formula is
IF(E7>=120000,E7*2,5%...
So all the figures greater or equal 120000 will be multiplied by 2.5% that is not correct.

The second condition E7>=150000 will not be computed because the first condition results TRUE for any number >=120000


M.

 

[KeithS.]

Thanks - The LOOKUP option actually got correct result - now that I verified a couple of them w/ a calculator. I think I see the error in the first formula; I'll try to tweak it.
Thanks again!

 

[Marcelo Branco]

Thanks - The LOOKUP option actually got correct result - now that I verified a couple of them w/ a calculator. I think I see the error in the first formula; I'll try to tweak it.
Thanks again!

You are welcome and thanks for the feedback

M.

 

[Biz]

Nice formula Marcelo.

I went 1 step further making table so changing sliding scale is easier.

Sheet1

	A	B	C	D	E	F	G	H	I
							Amounts	%

<tbody>
[TD="bgcolor: #CACACA, align: center"]1
[/TD]

[TD="bgcolor: #CACACA, align: center"]2
[/TD]
[TD="align: right"] 1,000,000
[/TD]
[TD="align: right"] 30,000
[/TD]
[TD="align: right"]30000
[/TD]

[TD="align: right"] -
[/TD]
[TD="align: right"]0.00%
[/TD]

[TD="bgcolor: #CACACA, align: center"]3
[/TD]

[TD="align: right"] 120,000
[/TD]
[TD="align: right"]2.50%
[/TD]

[TD="bgcolor: #CACACA, align: center"]4
[/TD]

[TD="align: right"] 150,000
[/TD]
[TD="align: right"]2.75%
[/TD]

[TD="bgcolor: #CACACA, align: center"]5
[/TD]

[TD="align: right"] 200,000
[/TD]
[TD="align: right"]3.00%
[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula B2 =A2*LOOKUP(A2,$H$2:$H$5,$I$2:$I$5) C2 =A2*LOOKUP(A2,{0;120000;150000;200000},{0;0.025;0.0275;0.03}) <tbody> </tbody>

<tbody>
</tbody>

Biz

 

[KeithS.]

So so much! Since this will be in a course used (mainly) by high school students, it looks like this latest formula will be the simplest.
Thanks again!

Keith