Formula Calculation

[dave]

Need formula that would calculate the following:
Scenerio: $69,000,000 in sales ( Cell A)
Commission( Cell B): .25% for 1st $50MM / .30% for amount between $50MM-
$60MM /.35% on amounts over $60MM

The value in cell B should be $ 186,500 ( $125,000 for 1st $50MM, $30,000 for
next $10MM, then $31,500 for the remainder $9MM

 

[Ron Rosenfeld]

Need formula that would calculate the following:
Scenerio: $69,000,000 in sales ( Cell A)
Commission( Cell B): .25% for 1st $50MM / .30% for amount between $50MM-
$60MM /.35% on amounts over $60MM

The value in cell B should be $ 186,500 ( $125,000 for 1st $50MM, $30,000 for
next $10MM, then $31,500 for the remainder $9MM

The most general method (and easy to add tiers or edit) is to set up a table
someplace on your worksheet like this:

$0 MM $0 0.25%
$50 MM $125,000.00 0.30%
$60 MM $155,000.00 0.35%

Column 2 is the commission that would be paid on amounts up to the amount shown
in column 1. It can be computed. If the CommissionTable is in J1:L3, then

K1: 0
K2: =(J2-J1)*L1+K1

and fill down to K3.

Then use this formula:


=VLOOKUP(A1,CommissionTable,2)+
VLOOKUP(A1,CommissionTable,3)*
(A1-VLOOKUP(A1,CommissionTable,1))
--ron