Hi,
If I'm reading this correctly, Table 1 has a list of break points at which the commission rate changes.
If the agent's sales are in the range of 4000 to 5500 (including the lower limit, but not including the upper limit), the commission due is 5%.
If the agent's sales are in the range of 5500 to 7000 (including the lower limit, but not including the upper limit), the commission due is 5.5%.
etc.
If that's the case, then LOOKUP is the function you are looking for, not IF.
Put your list of floor amounts in column A, starting with 0 in A2 (I'm assuming row 1 in a header row with an explanatory label).
Remove column B. As the next rate takes effect when the next floor rate is reached (at which point the 'ceiling' is breeched) the column is unnecessary.
Put the list of commission rates in column B.
Question: Do the rates apply ONLY to sales above the floor or to ALL sales?
Example:
Sales
000 3%
100 4%
200 5%
300 6%
Sales of $90 during the period earn $2.70 commission (90*3%)
Would sales of $250 during the same period earn commission of $12.50 (250*5%) or $9.50 (100*3% + 100*4% + 50*5%)
If the first pattern (all commissions paid at the rate for the highest floor amount exceeded by sales), then the first two are all that's needed.
If the second pattern applies, you'll need to break down the commission into two amounts: the commission paid up to the most recent floor amount ($7 for my example above) and the amount for sales at the current rate (50*5%=$2.50 in the example). This is most easily done adding a column to the lookup table.
Here's the example table again, with Column C added to take care of the second case where each rate applies to revenues within it's assigned range.:
Sales Rate Base
000 3% 0
100 4% =C2+(A3-A2)*B2
200 5% =C3+(A4-A3)*B3
300 6% =C4+(A5-A4)*B4
etc.
In Table 2:
Column D is a list of revenue made week-after-week (A different row for each week)
Column E is (D*F=$) With F being a %
Column F is where i want the formula. This should be a Commission % based off of the information in Column D
As described here, the first case above seems to apply.
The formula for F would be:
=LOOKUP(D,Table 1 :: $A,Table 1 :: $B)
For the second case, the same formula would apply, but the rate there would be the 'marginal' rate on the last amount earned.
The formula in E would be replaced by one that looked up the last 'floor' passed, the commission due on sales up to that floor, and the rate to be paid on the 'marginal' amount—the amount by which that floor was exceeded.
=LOOKUP(D,Table 1 :: $A,Table 1 :: $C)+LOOKUP(D,Table 1 :: $A,Table 1 :: $B)*(D-LOOKUP(D,Table 1 :: $A))
An
alternate method of applying an increasing commission rate on rising sales was discussed here in mid-December of last year. You may find that interesting as well. The link will take you directly to that discussion.
Regards,
Barry
