Earnings based on gross commission with split levels

[WaterWalk1]

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.

 

[barry houdini]

Try using the method suggested here

'McGimpsey & Associates : Excel : Using SUMPRODUCT() with variabl
rates' (http://www.mcgimpsey.com/excel/variablerate.html

 

[Ron Rosenfeld]

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