creating a function

N

NeilPoehlmann

i want to create a function that will calculate a commision of a number based
on %with different intervals. ex.
18,750- entered #, equals 731.25
0-5k=2.5%
5-10k=3.5%
10-15k=4.5%
15-20k=5.5%
were what ever number is entered it will cal based on actual # entered
 
B

Bob Phillips

Try this

=A2*LOOKUP(A2,{0,0.025;5000,0.05;10000,0.045;15000,0.055})

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
N

Neil Poehlmann

i assume "A2" is the field of entry, but the number isn't right. here is the
whole thing
$- 0 to $5,000.00 2.5%

$5,001.00 to $10,000.00 3.5%

$10,001.00 to $15,000.00 5.5%

$15,001.00 to $20,000.00 7.5%

$20,001.00 to $25,000.00 9.5%

$25,001.00 to $30,000.00 11.5%

$30,001.00 to $35,000.00 13.5%

$35,001.00 & Greater 15.0%
so 35,500 should be 2,750.00
 
B

Bob Phillips

The full formula should be

=A2*LOOKUP(A2,{0,0.025;5000,0.035;10000,0.055;15000,0.075;20000,0.095;25000,
0.115;30000,0.135;35000,0.15})

but I don't see where you get $2,750 from, I make it $5,325

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
N

Neil Poehlmann

Thanks- that was the ticket!! looks like sales commission ex. will be the
one, thanks again!
 
Top