IF Stmt. to calculate cumulative commissions

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.

See reply in .Functions

Biff

"Shams" <Shams@discussions.microsoft.com> wrote in message
news:C684C23B-25D8-4B8C-AB8B-733F0FE4FF9D@microsoft.com...
> 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.
>

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


"Shams" wrote:

> 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.
>

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

"Shams" wrote:

> 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.
>

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...

"B. R.Ramachandran" wrote:

> 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
>
> "Shams" wrote:
>
> > 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.
> >

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.

--
Greetings from New Zealand
Bill K

"B. R.Ramachandran" <BRRamachandran@discussions.microsoft.com> wrote in
message news:273A7232-04EF-4BEC-88DA-314E5823289B@microsoft.com...
> 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
>
> "Shams" wrote:
>
>> 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.
>>