Calculating multiple conditions

[DanR]

Hi:

I'm trying to use Excel to help me calculate bonuses for our firm. An example of how they might work is like this:

- In order to receive a $150 bonus, you must sell at least $5000 during a month.
- If you sell between $5000-$6000, you receive 10% of anything between $5k and $6k, in addition to the first $150.
- If you sell between $6k and $7k, you receive the first two amounts, plus 11% of anything between $6k and $7k.

The whole scheme continues up to $10k, with an additional percentage point added for amounts between $7k-$8k, $8k-$9k, $9k-$10k, etc.

The first one is easy: =IF(TotalSales>=5000),1500,0). It's the other ones I'm having trouble with, since I have to isolate amounts between two other amounts and give them each a particular percentage.

Any ideas?

Thanks!

Dan in San Antonio

[daddylonglegs]

Hello Dan,

Do you want one formula that will calculate the total bonus payable? If so try

=SUMPRODUCT((A1>{5000,6000,7000,8000,9000})+0,A1-{5000,6000,7000,8000,9000},{ 0.1,0.01,0.01,0.01,0.01})+IF(A1>5000,150)

where A1 contains the total sales amount

What happens after $10,000? The above will just keep calculating the bonus at 14%, perhaps you want to cap it so that no more is paid after 10000, in which case change to

=MIN(750,SUMPRODUCT((A1>{5000,6000,7000,8000,9000})+0,A1-{5000,6000,7000,8000,9000},{ 0.1,0.01,0.01,0.01,0.01})+IF(A1>5000,150))

[rylo]

Dan

See if the attached file gives you some direction.

rylo

[DanR]

Hi, DLL:

First, thanks very much for even taking the time to think about this.

I tried your formula (I wish I understood it a little better) and it give me a result that's CLOSE to the result I'm looking for, but not exactly the same.

Let me use an example and maybe you'll see something I don't:

Let's use $6,600 as an example:

For the first $5,000 we pay a set amount of $150 (if you don't reach $5000 we pay nothing);

For any amount between $5k and $6k, we pay 11%, in this case its the entire thousand dollars we're paying on, so that's an additional $110;

Finally (in this example) we would pay 12% of any amount between $6k and $7k, in this case $72.

So, we have $150 + $110 + $72 adding up to $332.

However, using your example the result I got was $316, and I can't figure out quite how you're getting that, so I don't know how to correct it.

Anyway, there you have it. Any further thoughts?

Thanks again!

- Dr

[DanR]

Hi, DLL:

First, thanks very much for even taking the time to think about this.

I tried your formula (I wish I understood it a little better) and it give me a result that's CLOSE to the result I'm looking for, but not exactly the same.

Let me use an example and maybe you'll see something I don't:

Let's use $6,600 as an example:

For the first $5,000 we pay a set amount of $150 (if you don't reach $5000 we pay nothing);

For any amount between $5k and $6k, we pay 11%, in this case its the entire thousand dollars we're paying on, so that's an additional $110;

Finally (in this example) we would pay 12% of any amount between $6k and $7k, in this case $72.

So, we have $150 + $110 + $72 adding up to $332.

However, using your example the result I got was $316, and I can't figure out quite how you're getting that, so I don't know how to correct it.

Anyway, there you have it. Any further thoughts?

Thanks again!

- Dr