Need help with percentages formula.

[frkre05]

Ok so I have this formula and it works, however I need it to do a bit more and cant figure it out

=IF(S99>2400,13%,8%)

so S99 is a cell with an average of money made after so many sales and if sales are an average of $2400 or higher it equals to 13% commission and if not it equals to base commission pay of 8%

I need something in the middle as well so
$1600-$1700 12%
$1800-$2099 11%
$1600-$1799 10%
and anything under $1599 8%

Thank you!

 

[dreid1011]

Your ranges don't make any sense.

You have 1600-1700 and then 1600-1799.

Otherwise, you can use a lookup like this:

=LOOKUP(S99,{0,1600,1700,1800,2400},{.08,.1,.11,.12,.13})*S99

Where the first set {} is the lowest number for each bracket, and the second set {} is the percentage for each bracket.

 

[frkre05]

Your ranges don't make any sense.

You have 1600-1700 and then 1600-1799.

Otherwise, you can use a lookup like this:

=LOOKUP(S99,{0,1600,1700,1800,2400},{.08,.1,.11,.12,.13})*S99

Where the first set {} is the lowest number for each bracket, and the second set {} is the percentage for each bracket.

$1599 or under 8%
$1600-&1799 10%
$1800-$2099 11%
$2100-$2399 12%
$$2400 and above 13%

So its adding the percentages and equals to like 65794.30%
I need it to be just 10% or 13% depends where it stands on the table but never to add the percentages....does this make sense??

 

[dreid1011]

Ok, so are you looking at 1 cell only (S99)? Or, are you dragging it down a column to show you what % each value in col S should be?

Either way, try this:

=LOOKUP(S99,{0,1600,1800,2100,2400},{.08,.1,.11,.12,.13})

 

[frkre05]

Ok, so are you looking at 1 cell only (S99)? Or, are you dragging it down a column to show you what % each value in col S should be?

Either way, try this:

=LOOKUP(S99,{0,1600,1800,2100,2400},{.08,.1,.11,.12,.13})

wow works like a charm thanks a bunch. what did it was removing the *S99 at the end like you did on the reply thanks again!!!

 

[dreid1011]

You're welcome. Glad it's working. Your original post was unclear, so I incorporated the *S99 to show you what the actual amount of the % would be.