Complex Commission Structure

[arkadd61]

I have a commission structure that I need to make some changes to. I've attached a copy of the spreadsheet I'm working on. I have an old formula for column "J" that I need to make some slight changes to and I don't remember how to do it. I also need to add a little something to it if its possible.

We are paid on a tiered structure as you will see from the formula. So as we attain the next tier threshold our commissions structure is bumped up to the next tier commissions retroactive back to the first of the moth. Below is what I would like the new spreadsheet to reflect in the appropriate columns.
Source Column: "I"
Result Column: "J"

I need column "J" to reflect the appropriate commission based on what price is entered into column "I". I also need the commission structure to automatically change back to the beginning once the next threshold is reached. as you can see below.

If its possible, if a sales rep were to make a sale at $39.99 or less, I would like the result in column "J" to reflect a flat commission amount of $55.00

Attachment:
Attachment 230764

Monthly Rate	1 to 10	11 to 26	27 to 42	42 +
$53.99	$310	$350	$370	$390
$52.99	$290	$330	$350	$370
$51.99	$270	$310	$330	$350
$50.99	$250	$290	$310	$330
$49.99	$230	$270	$290	$310
48.99	$210	$250	$270	$290
47.99	$190	$230	$250	$270
$46.99	$170	$210	$230	$250
$45.99	$150	$190	$210	$230
44.99	$130	$170	$190	$210
43.99	$120	$160	$180	$200
$42.99	$105	$145	$165	$185
$41.99	$90	$135	$155	$175
$40.99	$85	$125	$145	$165

[AB33]

If a sales rep were to make a sale at $39.99 or less,a flat commission amount of $55.00,So 39.99 is the minimum tier. What are these figures in columns next to monthly Rate? Are they the tier thresholds?
What is the tier for e.g. if a person sales 40.99?

[JBeaucaire]

1) Drop your reference table onto your sheet, I put it in columns V:Z.
2) Sort the column V ascending, and insert the 0 row at row2 for your $55
3) Correct the titles in W1:Z1 to indicate the START of each tier, not the range. The range is implied by the start of each tier.

4) Now entire this formula in J2 and copy down:
=INDEX($W$2:$Z$16, MATCH([@MMR], $V$2:$V$16, 1), MATCH($R$2, $W$1:$Z$1, 1))

[arkadd61]

Thank you for your assistance! It works perfectly, except for one little thing. In my spreadsheet in column "I" if I don't put a value the formula you provided automatically put a commission amount of $55.00. How do we adjust the formula to leave this blank until there is a value placed into column "I"?

[arkadd61]

Yes these are tier thresholds. If you look at the top on the chart in the post, these are the tier threshold ranges. and the commissions associated with them.

[JBeaucaire]

Just add a little test to the start of that formula in J2:

=IF(I2="", "", INDEX($W$2:$Z$16, MATCH([@MMR], $V$2:$V$16, 1), MATCH($R$2, $W$1:$Z$1, 1)))



If your original question has been answered, please select Thread Tools from the links above and mark this thread as SOLVED. Thanks.