SOLVED

IF formulas

New Contributor

Can someone help me with how to set up IF when I am trying to calculate no rebate if revenue is $99,000 or less, a 7.5% rebate back to $1 if revenue is between $100,000 and $200,000, then if revenue is more than $200,000 add a 10% rebate on the amount greater than $200,000 up to $300,000, and a 12% rebate on any revenue above $300,000.

Example,

if revenue is $150,000, then rebate is 7.5% *$150,000

if revenue is $250,000 then rebate is 7.5% * $200,000 plus 10% * $50,000

if revenue is $350,000, then rebate is 7.5% *$200,000 plus 10% * $100,000 plus 12% * $50,000

2 Replies
best response confirmed by Reggie58 (New Contributor)
Solution

@Reggie58 

Let's say you have an amount in A2. The rebate is given by

=SUMPRODUCT((A2>={0,100000,200000,300000})*(A2-{0,0,200000,300000}),{0,0.075,0.025,0.02})

@Hans Vogelaar 

Thank you.