Solved

Graduated Commission Calculation

Posted on 2013-01-19
3
1,856 Views
Last Modified: 2013-01-19
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
0
Comment
Question by:Cactus1994
  • 2
3 Comments
 
LVL 26

Accepted Solution

by:
redmondb earned 500 total points
ID: 38797783
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
 

Author Closing Comment

by:Cactus1994
ID: 38797804
Hi Brian:

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

Thanks.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38797808
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

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now