software

I have a bit of a quandry with this one. I have a spreadsheet I need to
calculate commissions with. First the salespeople enter in how many sales
they get each day which then totals at the bottom. The way it works for most
of these sales is if they get between 1-4 sales, they will get $2 for each
sale. Above and beyond 4, they get $5 for each sale. I have the forumla
setup like this:

=IF(B28lt;4,B28*2,6 (B28-3)*5) where B28 is the total number of units sold.

The problem is I have one product where the commissions are on three tiers:

1-5 units= $7
6-10 units= $10
11 units= $13

How would I calculate this?

www.mcgimpsey.com/excel/variablerate.html

--
Kind regards,

Niek Otten

quot;brodiemacquot; gt; wrote in message
...
gt;I have a bit of a quandry with this one. I have a spreadsheet I need to
gt; calculate commissions with. First the salespeople enter in how many sales
gt; they get each day which then totals at the bottom. The way it works for
gt; most
gt; of these sales is if they get between 1-4 sales, they will get $2 for each
gt; sale. Above and beyond 4, they get $5 for each sale. I have the forumla
gt; setup like this:
gt;
gt; =IF(B28lt;4,B28*2,6 (B28-3)*5) where B28 is the total number of units sold.
gt;
gt; The problem is I have one product where the commissions are on three
gt; tiers:
gt;
gt; 1-5 units= $7
gt; 6-10 units= $10
gt; 11 units= $13
gt;
gt; How would I calculate this?
I like to restructure the calculation as $7 per unit, PLUS $3 per unit ($10 -
$7) above 5, PLUS $3 per unit ($13 - $10) above 10.
Then the formula is =7*b28 max(0,3*(b28-5)) max(0,3*(b28-10)).
Each MAX ensures that there's no penalty for not reaching that threshold.
--Bruce

quot;brodiemacquot; wrote:

gt; I have a bit of a quandry with this one. I have a spreadsheet I need to
gt; calculate commissions with. First the salespeople enter in how many sales
gt; they get each day which then totals at the bottom. The way it works for most
gt; of these sales is if they get between 1-4 sales, they will get $2 for each
gt; sale. Above and beyond 4, they get $5 for each sale. I have the forumla
gt; setup like this:
gt;
gt; =IF(B28lt;4,B28*2,6 (B28-3)*5) where B28 is the total number of units sold.
gt;
gt; The problem is I have one product where the commissions are on three tiers:
gt;
gt; 1-5 units= $7
gt; 6-10 units= $10
gt; 11 units= $13
gt;
gt; How would I calculate this?

Hi

One way
=A1*7 (MAX(0,A1-5)*3 (MAX(0,(A1-10)*3

Your first formula expressed in the same way would be
=A1*2 MAX(0,A1-4)*3

The formula pays the base amount on all sales, then the incremental
value on the sales above each incremental step.

--
Regards

Roger Govierquot;brodiemacquot; gt; wrote in message
...
gt;I have a bit of a quandry with this one. I have a spreadsheet I need
gt;to
gt; calculate commissions with. First the salespeople enter in how many
gt; sales
gt; they get each day which then totals at the bottom. The way it works
gt; for most
gt; of these sales is if they get between 1-4 sales, they will get $2 for
gt; each
gt; sale. Above and beyond 4, they get $5 for each sale. I have the
gt; forumla
gt; setup like this:
gt;
gt; =IF(B28lt;4,B28*2,6 (B28-3)*5) where B28 is the total number of units
gt; sold.
gt;
gt; The problem is I have one product where the commissions are on three
gt; tiers:
gt;
gt; 1-5 units= $7
gt; 6-10 units= $10
gt; 11 units= $13
gt;
gt; How would I calculate this?
Another option:
=MAX(7*A1,10*A1-15,13*A1-45)

HTH :gt;)
--
Dana DeLouis
Win XP amp; Office 2003quot;brodiemacquot; gt; wrote in message
...
gt;I have a bit of a quandry with this one. I have a spreadsheet I need to
gt; calculate commissions with. First the salespeople enter in how many sales
gt; they get each day which then totals at the bottom. The way it works for
gt; most
gt; of these sales is if they get between 1-4 sales, they will get $2 for each
gt; sale. Above and beyond 4, they get $5 for each sale. I have the forumla
gt; setup like this:
gt;
gt; =IF(B28lt;4,B28*2,6 (B28-3)*5) where B28 is the total number of units sold.
gt;
gt; The problem is I have one product where the commissions are on three
gt; tiers:
gt;
gt; 1-5 units= $7
gt; 6-10 units= $10
gt; 11 units= $13
gt;
gt; How would I calculate this?
So many answers so far. You are all great.

I used the answer that Roger Govier gave since it worked the best for me.
Thanks all!!!

quot;Roger Govierquot; wrote:

gt; Hi
gt;
gt; One way
gt; =A1*7 (MAX(0,A1-5)*3 (MAX(0,(A1-10)*3
gt;
gt; Your first formula expressed in the same way would be
gt; =A1*2 MAX(0,A1-4)*3
gt;
gt; The formula pays the base amount on all sales, then the incremental
gt; value on the sales above each incremental step.
gt;
gt; --
gt; Regards
gt;
gt; Roger Govier
gt;
gt;
gt; quot;brodiemacquot; gt; wrote in message
gt; ...
gt; gt;I have a bit of a quandry with this one. I have a spreadsheet I need
gt; gt;to
gt; gt; calculate commissions with. First the salespeople enter in how many
gt; gt; sales
gt; gt; they get each day which then totals at the bottom. The way it works
gt; gt; for most
gt; gt; of these sales is if they get between 1-4 sales, they will get $2 for
gt; gt; each
gt; gt; sale. Above and beyond 4, they get $5 for each sale. I have the
gt; gt; forumla
gt; gt; setup like this:
gt; gt;
gt; gt; =IF(B28lt;4,B28*2,6 (B28-3)*5) where B28 is the total number of units
gt; gt; sold.
gt; gt;
gt; gt; The problem is I have one product where the commissions are on three
gt; gt; tiers:
gt; gt;
gt; gt; 1-5 units= $7
gt; gt; 6-10 units= $10
gt; gt; 11 units= $13
gt; gt;
gt; gt; How would I calculate this?
gt;
gt;
gt;

Hi

Thanks for the feedback.
My solution had some extraneous brackets included, and would have
resulted in an error if used as posted. It should have read
either =A1*7 (MAX(0,A1-5)*3) (MAX(0,A1-10)*3)
or more simply =A1*7 MAX(0,A1-5)*3 MAX(0,A1-10)*3
Bruce's solution was identical to mine, but with the correct number of
brackets.
Dana also gave the correct result, and requires the fewest typing of
brackets, which as you can see, I sometimes get wronglt;bggt;

--
Regards

Roger Govierquot;Roger Govierquot; gt; wrote in message
...
gt; Hi
gt;
gt; One way
gt; =A1*7 (MAX(0,A1-5)*3 (MAX(0,(A1-10)*3
gt;
gt; Your first formula expressed in the same way would be
gt; =A1*2 MAX(0,A1-4)*3
gt;
gt; The formula pays the base amount on all sales, then the incremental
gt; value on the sales above each incremental step.
gt;
gt; --
gt; Regards
gt;
gt; Roger Govier
gt;
gt;
gt; quot;brodiemacquot; gt; wrote in message
gt; ...
gt;gt;I have a bit of a quandry with this one. I have a spreadsheet I need
gt;gt;to
gt;gt; calculate commissions with. First the salespeople enter in how many
gt;gt; sales
gt;gt; they get each day which then totals at the bottom. The way it works
gt;gt; for most
gt;gt; of these sales is if they get between 1-4 sales, they will get $2 for
gt;gt; each
gt;gt; sale. Above and beyond 4, they get $5 for each sale. I have the
gt;gt; forumla
gt;gt; setup like this:
gt;gt;
gt;gt; =IF(B28lt;4,B28*2,6 (B28-3)*5) where B28 is the total number of units
gt;gt; sold.
gt;gt;
gt;gt; The problem is I have one product where the commissions are on three
gt;gt; tiers:
gt;gt;
gt;gt; 1-5 units= $7
gt;gt; 6-10 units= $10
gt;gt; 11 units= $13
gt;gt;
gt;gt; How would I calculate this?
gt;
gt;