Hello,
I need some help with formulas to calculate commission for an employee.
They have a sheet allocated to each financial quarter, with columns for gross and net revenue.
These totals are populated to the summary table, where I'm trying to calculate the following based on their Net Revenue:
$0 - $12,500 = 0%
$12501-$27500 = 15%
$27501-$37500 = 22.5%
$37501 and over = 30%
I can't work out the formulas for columns F, G and H in the Summary Sheet. Any help would be enormously appreciated!
Try in F3:
=MIN($E3,15000)*0.15
G3:
=MIN($E3-15000,10000)*0.225
H3:
=MIN($E3-25000,15000)*0.3
Your manual result in F3 = 2,249.85 = 14999*0.15 seems not correct, it should be 15000*0.15 as per my formula did.
Quang PT
In F3
=MAX(0,(MIN(27500,$C3)-MIN(12500,$C3))*15%)
In G3
=MAX(0,(MIN(37500,$C3)-MIN(27500,$C3))*22.5%)
In H3
=MAX(0,($C3-37500)*22.5%)
pull down all.
Pl note
Array formula should be confirmed with Ctrl+Shift+Enter keys together.
If answere is satisfactory press * to add reputation.
FWIW, should you ever want to combine into a single-cell calculation, and/or you ever find yourself with lots of tiers, you can use the SUMPRODUCT approach
=SUMPRODUCT(--(D3>={0,12500,27500,37500}),D3-{0,12500,27500,37500},{0,0.15,0.075,0.075})
explanation : http://www.mcgimpsey.com/excel/variablerate.html
but, to reiterate, this approach is more useful where you have lots of bands to deal with -- i.e. provided above only for sake of illustration / completeness.
TRY F3 cell formula
G3 cell formulaHTML Code:
H3 cell formulaHTML Code:
HTML Code:
Thank you for this bebo021999. This is working fine unless there's no value for the tiers. Instead of returning a "$0" value, it returns a -ive dollar value. How can I amend the formulas to reflect that if no commission applies to the tier, return a value of $0?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks