I think I'm getting more feeble as I used to be able to figure this out on my own. I have a tiered rebate program with a customer similar to the following:
1,000 to 1,999 in sales, 2%
2,000 to 2,999 in sales, 2.5%
3,000 to unlimited in sales, 3.0%
The rebates are incremental, meaning that if sales reached $2,500 the rebate would be calculated thusly: 2% of $1,999 = $39.98 plus 2.5% of $501 = $10.02. Total rebate = $50.00
If sales reached $3,400, the rebate would be: 2% of $1,999 = $39.98, plus 2.5% of $1000 = $25.00, plus 3.0% of $401 = $12.03. Total rebate = $77.01.
How can I automate this process with a formula? Thank you all in advance for your support!
1,000 to 1,999 in sales, 2%
2,000 to 2,999 in sales, 2.5%
3,000 to unlimited in sales, 3.0%
The rebates are incremental, meaning that if sales reached $2,500 the rebate would be calculated thusly: 2% of $1,999 = $39.98 plus 2.5% of $501 = $10.02. Total rebate = $50.00
If sales reached $3,400, the rebate would be: 2% of $1,999 = $39.98, plus 2.5% of $1000 = $25.00, plus 3.0% of $401 = $12.03. Total rebate = $77.01.
How can I automate this process with a formula? Thank you all in advance for your support!