Apply various percentage with sales between a & b, b & c, etc.
 

I want the bonus percentage to change from zero to 1% with sales are between
$70,000 and $89,999.99
to 2% when between $90,000 and $119,999.99
to 4% when between $120,000 and $144,999.99
to 5% when between $145,000 and $174,999.99
to 7% when between $175,000 and $204,999.99
and 9% when between $205,000 and $5,000,000
The total sales cell is E4 and the Bonus Percentage cell is O5
Thanks for the help.
--
Rick R

Apply various percentage with sales between a & b, b & c, etc.
 

You might need more than just the 1 cell for the Bonus Percentage. If I
understand you correctly, if someone were, for example to have sales at
150,000, they would receive 1% for the 70,000-89,999.99, 2% for the
90,000-119,999.99, 4% for the 120,000-144,999.99 and 5% for the
145,000-150,000. Is this correct? Or if the higher they go in sales, just the
higher commission rate they get. In that case, you could use this formula in
O5:
=LOOKUP(E4,{0,0;70000,0.01;90000,0.02;120000,0.04; 145000,0.05;175000,0.07;205000,0.09})
--
** John C **


"Rick R" wrote:

I want the bonus percentage to change from zero to 1% with sales are between
$70,000 and $89,999.99
to 2% when between $90,000 and $119,999.99
to 4% when between $120,000 and $144,999.99
to 5% when between $145,000 and $174,999.99
to 7% when between $175,000 and $204,999.99
and 9% when between $205,000 and $5,000,000
The total sales cell is E4 and the Bonus Percentage cell is O5
Thanks for the help.
--
Rick R

Apply various percentage with sales between a & b, b & c, etc.
 

John. You are wonderful. The formula you proposed was perfect! Thanks so
much for the help. You saved me tons of labor and worry.
--
Rick R


"Rick R" wrote:

I want the bonus percentage to change from zero to 1% with sales are between
$70,000 and $89,999.99
to 2% when between $90,000 and $119,999.99
to 4% when between $120,000 and $144,999.99
to 5% when between $145,000 and $174,999.99
to 7% when between $175,000 and $204,999.99
and 9% when between $205,000 and $5,000,000
The total sales cell is E4 and the Bonus Percentage cell is O5
Thanks for the help.
--
Rick R