Rebate formula help

[johndowd]

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!

 

[Tetra201]

Is this what you need?

=SUMPRODUCT(--(A1>={1000,2000,3000}),(A1-{0,1999,2999}),{0.02,0.005,0.005})

By the way, $2,500 --> $52.51

 

[johndowd]

Tetra - Thank you for the response, and the correction on my math! Unfortunately I can't get the formula to work. I've entered it exactly as you proposed, and entered a value greater than 1,000 in cell A1, but all I get is a #VALUE! error. Any thoughts?

 

[johndowd]

Upon further editing, I got it to work perfectly! My problem was the values used in the example were much smaller than the actual numbers, and I entered them incorrectly. Your formula as it is is correct, just my numbers were bad!

Thanks again! It was a real assist!

 

[Tetra201]

You are welcome.