jski
Board Regular
- Joined
- Jan 11, 2006
- Messages
- 118
I'm attempting to make a matrix calculate properly. The calculation is cumulative, with a new percentage used at certain thresholds:
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.
| 2008 tap spreadsheet Test 3.xls | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|
| D | E | F | G | H | I | J | K | |||
| 63 | CumulativetotaloftheYTDIncentivePayment | |||||||||
| 64 | Thresholds | Q1 | Q2 | Q3 | Q4 | |||||
| 65 | 0-$49,999 | Payout%: | 5% | $1,526.58 | $1,754.69 | $1,754.69 | $1,754.69 | |||
| 66 | $50,000-$99,999 | Payout%: | 10% | $3,053.17 | $3,509.38 | $3,509.38 | $3,509.38 | |||
| 67 | $100,000-$149,000 | Payout%: | 15% | $4,579.75 | $5,264.07 | $5,264.07 | $5,264.07 | |||
| 68 | $150,000-199,999 | Payout%: | 20% | $6,106.33 | $7,018.76 | $7,018.76 | $7,018.76 | |||
| 69 | $200,000-$249,999 | Payout%: | 25% | $7,632.92 | $8,773.45 | $8,773.45 | $8,773.45 | |||
| 70 | $250,000-$299,999 | Payout%: | 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.
