IF Stmt. to calculate cumulative commissions

[Guest]

Folks,
I am trying to write an IF statement that will do the following:

It calculates a commission amount based on 3 level of Sales:

If Sales is < = $1,500, calculate 0.0025*1500

If Sales is < = $3,000, calculate 0.0045*3000

If Sales is > $3,000, calculate 0.0050* cell reference...

Now, this may look easy enough..here's the trick that needs to happen

Say, sales is $5,500..the commission calculated should be cumulative i.e.
0.0025 * 1500, + .0045*3,000 + .0050*(remainder amount)

What would be a clean way of doing this? I will appreciate any help. Thanks.

Regards,
Shams.

 

[Biff]

See reply in .Functions

Biff

 

[Guest]

Hi Shams,

Try the following formula. It is configured assuming that the sales info is
contained in A1; change it appropriately.

=IF(A1>3000,10.5+(A1-3000)*0.005,IF(A1>1500,3.75+(A1-1500)*0.0045,A1*0.0025))

Regards,
B. R. Ramachandran

 

[Guest]

Hi Shams,
I think I misunderstood your posting a liitle bit in my previous reponse.
I thought that the commission is 0.0025 for upto 1500, 0.0045 for any amount
in excess of 1500 (and less than 3000), and 0.005 for about 3000.
For example, if the total sales amount is 3500, the commission is
0.0025*1500+0.0045*the next 1500+0.0050*500.
However, I now see from your example, that if the sales amount is 5500, the
commission is 0.0025*1500+0.0045*3000 (and not the next 1500) +0.0050* the
remainder (I assume that you mean the amount in excess of 3000). If it is so
use the following formula:

=IF(A1>3000,17.25+(A1-3000)*0.005,IF(A1>1500,3.75+(A1-1500)*0.0045,A1*0.0025))

Sorry about the confusion.
Regards,
B.R. Ramachandran

 

[Guest]

Thanks for your elaborate reply. I used your suggestion but set up my
formula slightly different. It works. So, thank you very much for pointing
me at the right direction...

 

[Bill Kuunders]

Shams
Using an example from JE McG

=SUMPRODUCT(--(A1>{0,1500,3000}),(A1-{0,1500,3000}),
{0.0025,0.00325,0.00175})
where the last values are the increments between the commission rates.
As B.R.R indicates it is not very clear what you mean by the "remainder
amount".

It would be good if you could give us the result you expect from your
example.
The above formula will give you 31.125.