need help making a formula

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

kath - 04 Mar 2008 19:04 GMT

i need to make a formula that will show a commission rate of 5% sales over
15,000 with a 3% sales commission for lesser sales. Could someone please
help me with this.

Reply to this Message

Ron Coderre - 04 Mar 2008 19:09 GMT

A1: (a sales amount)

B1: =A1*.03+MAX(A1-15000,0)*.02

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

>i need to make a formula that will show a commission rate of 5% sales over
> 15,000 with a 3% sales commission for lesser sales. Could someone please
> help me with this.

Reply to this Message

Ron Coderre - 04 Mar 2008 19:13 GMT

Actually, if you use what I posted, it will fix itself.

This:
B1: =A1*.03+MAX(A1-15000,0)*.02

will become this:
B1: =A1*0.03+MAX(A1-15000,0)*0.02

(zeros preceding the decimal points)

--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

> A1: (a sales amount)
>
[quoted text clipped - 12 lines]
>> 15,000 with a 3% sales commission for lesser sales. Could someone please
>> help me with this.

Reply to this Message

T. Valko - 04 Mar 2008 19:25 GMT

Does that mean if the sales were 27,336 -

3% of the first 15,000 + 5% of the next 12,336 ?

If that's the case:

A1 = 27,336

=IF(A1="",0,MIN(A1,15000)*0.03+MAX(A1-15000,0)*0.05)

Commisson = 1066.80

Signature

Biff
Microsoft Excel MVP

>i need to make a formula that will show a commission rate of 5% sales over
> 15,000 with a 3% sales commission for lesser sales. Could someone please
> help me with this.

Reply to this Message