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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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