Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Earnings based on gross commission with split levels
I need help with a formula that calculates earnings based on gross commission
with split levels. For example A gross commission less than 100,000 up to 100,000 makes 50% €¦a gross commission greater than 100,000 will pay as follows€¦the 2nd 100,000 (100,001 to 200,000 makes 60%...the next 100,000 (200,001 €“ 300,000) makes 70%, so on and so forth€¦ So with a 400,000 gross commission: The first 100,000 will pay at 50% = $50,000 The 2nd 100,000 will pay at 60% = $60,000 The 3rd 100,000 will pay at 70% = $70,000 The 4th 100,000 will pay at 80% = $80,000 Total Earnings€¦€¦€¦€¦€¦€¦€¦€¦.$260,000 I need the formula to calculate so that if any of the variables (commission amounts or split levels) change, the earnings automatically change for all the individuals. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Earnings based on gross commission with split levels
Try using the method suggested here 'McGimpsey & Associates : Excel : Using SUMPRODUCT() with variable rates' (http://www.mcgimpsey.com/excel/variablerate.html) -- barry houdini ------------------------------------------------------------------------ barry houdini's Profile: http://www.thecodecage.com/forumz/member.php?userid=72 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=145733 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Earnings based on gross commission with split levels
On Mon, 19 Oct 2009 12:17:01 -0700, WaterWalk1
wrote: I need help with a formula that calculates earnings based on gross commission with split levels. For example A gross commission less than 100,000 up to 100,000 makes 50% …a gross commission greater than 100,000 will pay as follows…the 2nd 100,000 (100,001 to 200,000 makes 60%...the next 100,000 (200,001 – 300,000) makes 70%, so on and so forth… So with a 400,000 gross commission: The first 100,000 will pay at 50% = $50,000 The 2nd 100,000 will pay at 60% = $60,000 The 3rd 100,000 will pay at 70% = $70,000 The 4th 100,000 will pay at 80% = $80,000 Total Earnings…………………….$260,000 I need the formula to calculate so that if any of the variables (commission amounts or split levels) change, the earnings automatically change for all the individuals. This kind of problem is often solved using a lookup table. However, in the information you post, you do not indicate what the percentage is for amounts more than $400,000. I assumed 90%, but you might want to change that. In any event, set up a table someplace on your sheet I NAME'd it "Tbl" Tbl looks like this: $0 $0 50% $100,000 $50,000 60% $200,000 $110,000 70% $300,000 $180,000 80% $400,000 $260,000 90% Column 2 is the amount to be paid with earnings from column 1. So with the table in H1:J4 H1: 0 I1: 0 J1: 50% H2: 100000 I2: =(H2-H1)*J1+I1 J2: 50% Fill in the rest of col H and col J; and copy down I2 to I4. Then use this formula: =VLOOKUP(A1,Tbl,2)+(A1-VLOOKUP(A1,Tbl,1))*VLOOKUP(A1,Tbl,3) --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula for Gross Profit % to commission % based on GP $ | Excel Worksheet Functions | |||
figuring commissions based on gross sales | Excel Worksheet Functions | |||
Calculate gross profit based on varying percentages | Excel Worksheet Functions | |||
How do I create a commission chart based on dates worked? | Excel Worksheet Functions | |||
calculate commission $ based on total sold and commission percent | Excel Programming |