# Threshold Matrix Calculation

#### jski

##### Board Regular
I'm attempting to make a matrix calculate properly. The calculation is cumulative, with a new percentage used at certain thresholds:
DEFGHIJK
63CumulativetotaloftheYTDIncentivePayment
64ThresholdsQ1Q2Q3Q4
650-\$49,999Payout%:5%\$1,526.58\$1,754.69\$1,754.69\$1,754.69
66\$50,000-\$99,999Payout%:10%\$3,053.17\$3,509.38\$3,509.38\$3,509.38
67\$100,000-\$149,000Payout%:15%\$4,579.75\$5,264.07\$5,264.07\$5,264.07
68\$150,000-199,999Payout%:20%\$6,106.33\$7,018.76\$7,018.76\$7,018.76
69\$200,000-\$249,999Payout%:25%\$7,632.92\$8,773.45\$8,773.45\$8,773.45
70\$250,000-\$299,999Payout%:30%\$9,159.50\$10,528.14\$10,528.14\$10,528.14
71\$300,000+Payout%:35%\$10,686.08\$12,282.83\$12,282.83\$12,282.83
sheet 1 (2)

Here's how the matrix would be used. Cells H65 through K71 calculate the cumulative payouts by quarter based on a persons sales. For example, if a person's sales for quarter 1 were \$60K, they would receive an incentive of 5% against the first \$49,999 and 10% for the next \$20K. Once a threshold is reached all future incentives are calculated from dollar one (in this example, this individual would be paid 10% on all future sales until they reached \$100K. They would then be paid 15%, etc.)

Right now the array H65:K71 simply applies a percentage against a total from another cell in the spreadsheet which is incorrect. The solution likely requires a complex IF/THEN statement, but I'm having a little difficulty putting it together. Any assistance from the community would be helpful. Thanks in advance for all contributions.

### Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

#### DonkeyOte

##### MrExcel MVP
presumably you must have a table of historic sales by person by date of sale ?
you would need this to be able to calculate at which point accelerat

presumably given the layout of the matrix the incentives are "reset" each quarter ?

could you provide samples of the sales table so the guys and gals here can provide some calcs for you based on these other key ranges ?

thanks

#### jski

##### Board Regular

presumably you must have a table of historic sales by person by date of sale ?
you would need this to be able to calculate at which point accelerat

presumably given the layout of the matrix the incentives are "reset" each quarter ?

No. The person continues receiving the current threshold payout until the next one is reached.

could you provide samples of the sales table so the guys and gals here can provide some calcs for you based on these other key ranges ?

The "table" is filled out quarterly by the salesperson and looks like this:
HIJK
9FeeCreditApplied
10Q1Q2Q3Q4
11\$3,068.74
12\$5,688.33
13\$8,539.42
14\$8,673.05
15\$4,562.13\$4,562.13
16\$-
17\$-
18\$-
19\$-
20\$-
21\$-
22\$-
23\$-
24\$-
25\$-
26\$-
27\$-
28
29\$30,531.67\$4,562.13\$-\$-
sheet 1 (2)

Cells H29:K29 provide the totals I would be working with from quarter to quarter.

Replies
0
Views
272
Replies
3
Views
1K
Replies
1
Views
806
Replies
11
Views
2K
Replies
1
Views
136

1,191,368
Messages
5,986,256
Members
440,015
Latest member
knijgh

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back