Need a formula to calculate Average Sales Commission

  • Thread starter Thread starter sam1259
  • Start date Start date
a different approach:

total sales in cell b11

cells a1:a4 are your commission break points - 0, 30000, 60000, 90000
cell a5 is =IF(B11>A4,B11,0)

cell b1 is 0
cell 2 is =IF($B$11>A2,A2,$B$11) - copy this down to cell b5

cells c1:c5 are the commission %'s 0, 50, 65, 80, 90

cell d1 is 0
cell d2 is =IF(B2=0,0,IF($B$11>B2,B2-B1,$B$11-B1)) - copy down to cel
d5

cell e1 is 0
cell e2 is =d2*c2 - copy down to cell e5

avg % is sum(e1:e5)/b11

this allows you to easily change the break point
 
Hi Duane!

That's pretty well where I started from but I telescoped it all int
one formula 'cos I felt like it at the time!

Al
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

calculating commissions 2
Need a formula to calculate Average Sales Commission 2
IF Stmt. for cumulative commissions. 4
Trying to multiply data by one cell, based on data of another 3
Commissions and lookup table 3
Help creating a sales commission formula 3
Formula Calculation 1
vlookup? 5

Back
Top