Calculate comission graduated scale, $2000=20%, $4000=40%, etc?

I need to set up a spreadsheet in Excel 2003 to calculate comission of sales
on a graduated scale. Salesman sells $0-2000, he receives 20% commission;
$2000.01-4000, 40%; etc. I know it's simple, but I'm stuck. Any help is
appreciated. Thank you.

> .. I know it's simple ..
And it may not be as simple as it seems ..

Try JE's page at:
http://www.mcgimpsey.com/excel/variablerate.html
which shows the way to handle it
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"kalsolelady" <[email protected]> wrote in message
news:[email protected]...
> I need to set up a spreadsheet in Excel 2003 to calculate comission of
sales
> on a graduated scale. Salesman sells $0-2000, he receives 20% commission;
> $2000.01-4000, 40%; etc. I know it's simple, but I'm stuck. Any help is
> appreciated. Thank you.

Hi!

Here's another method.

Assume sale amount is in A1.

=A1*LOOKUP(A1,{0,0.2;2000.01,0.4})

You use the lower boundary of each commission range. In the above formula
any amount over 2000.01 will recieve a 40% commission. If you have another
range, say, 4000.01 and greater @ 45%:

=A1*LOOKUP(A1,{0,0.2;2001,0.4;4000.01,0.45})

Just add ranges as needed.

Biff

"kalsolelady" <[email protected]> wrote in message
news:[email protected]...
>I need to set up a spreadsheet in Excel 2003 to calculate comission of
>sales
> on a graduated scale. Salesman sells $0-2000, he receives 20% commission;
> $2000.01-4000, 40%; etc. I know it's simple, but I'm stuck. Any help is
> appreciated. Thank you.

You may well be right, Biff, but "something" <g>
hints to me that the comm calcs will probably follow these lines:

Sales: $2,200
Comm from 1st tier: $2000 x 20% = $400
Comm from 2nd tier: $200 x 40% = $80
Total comm: $400 x $80 = $480

Rather than:

Sales: $2,200
Comm from 2nd tier: $2,200 x 40% = $880

That's why I guessed it's not so simple ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

Oops, typo in line:
> Total comm: $400 x $80 = $480

should read as:
> Total comm: $400 + $80 = $480

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----