creating a function
 

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

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)


"NeilPoehlmann" wrote in message
...
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

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

"Bob Phillips" wrote:

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)


"NeilPoehlmann" wrote in message
...
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

The full formula should be

=A2*LOOKUP(A2,{0,0.025;5000,0.035;10000,0.055;1500 0,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)


"Neil Poehlmann" wrote in message
...
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

"Bob Phillips" wrote:

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)


"NeilPoehlmann" wrote in
message
...
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

That's because it's a progressive formula: only the amount over 35,000
has the 15% rate applied, only the amount between 30,000 and 35,000 has
the 13.5% rate applied. See

http://www.mcgimpsey.com/excel/variablerate.html

for a solution.

In article ,
"Bob Phillips" wrote:


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

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

"JE McGimpsey" wrote:

That's because it's a progressive formula: only the amount over 35,000
has the 15% rate applied, only the amount between 30,000 and 35,000 has
the 13.5% rate applied. See

http://www.mcgimpsey.com/excel/variablerate.html

for a solution.

In article ,
"Bob Phillips" wrote:


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