Graduated Commission Calculation

The attached spreadsheet shows a simple declining rate commission table.

I just need to know a formula for how to calculate the commission based on the tiered rates in the table. I've included an example result on the spreadsheet.

Thanks in advance!
Commission-Calc.xls
Tim JackoboiceOwnerAsked:
Who is Participating?
 
redmondbConnect With a Mentor Commented:
Hi, Cactus1994.

A long-winded answer...
=IF(B1>B7,(B1-B7)*C8,0)+IF(B1>B6,(MIN(B1,B7)-B6)*C7,0)+IF(B1>B5,(MIN(B1,B6)-B5)*C6,0)+MIN(B1,B5)*C5
...which could be siplified if it could be guaranteed that B4 and B9 were empty.

Regards,
Brian.
0
 
Tim JackoboiceOwnerAuthor Commented:
Hi Brian:

Long-winded, but dead on ... and exactly what I needed!

Thanks.
0
 
redmondbCommented:
Thanks, Cactus1994.

The attached is a shorter, but more complicated array-entered formula...
=SUM(IF(B1>B4:B7,(IF(B1<B5:B8,B1,B5:B8)-B4:B7)*C5:C8,0))

This formula makes it much easier to increase/decrease the table's entries. It requires a small hack in that B4 appears to be a string ("But Not Over") but is actually zero - the string is actually a custom number format.

Please feel free to post here any queries on either formula.

Regards,
Brian.
Commission-Calc-V2.xls
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.