Need help with formula for rebate

[Need_Excelhelp]

Hi,

I need help with creating formula to calculate rebates. Numbers are toned down for simplicity purpose.

The scenario is that for the first $10000 spent, the customer receives 2% discount, and for every additional $500 spent the rebate varies based on the promotional offer (varied rates are given in a one of the columns).

The set up is as follows:

A B C
Total Spent Varied rate Rebate Amt.

Data starts from row 2, and first row is labels.

I would really appreciate any help that's provided!

Thank you!

 

[Joe4]

Welcome to the Board!

If you provide an actual example with all the details and expected results (you started to, then kind of trailed off on the details for the "additional" amounts), we can help you craft a formula to do what you want.

 

[West Man]

Sorry

 

[West Man]

It is not totally clear how you wish the rebate to be computed. Does this work?

[TABLE="width: 236"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Spent[/TD]
[TD]Var. Rate[/TD]
[TD]Rebate[/TD]
[/TR]
[TR]
[TD]17000[/TD]
[TD]0.01[/TD]
[TD]270[/TD]
[/TR]
[TR]
[TD]5000[/TD]
[TD]0.01[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]20000[/TD]
[TD]0.01[/TD]
[TD]300[/TD]
[/TR]
</tbody>[/TABLE]

Rebate formula: =(A2>=10000)*200+(MAX(0,500*(INT((A2-10000)/500)))*B2)

 

[Need_Excelhelp]

Thank you both for your help!

@joe4 The table looks like what West man mentioned. But this is what I meant for every additional amount: A customer buys $10500, $20000, $24600, $20150 worth of products. So for the first $10000, the rebate is 2% and for every additional $500 they get additional 1%, 2%,3% rebate based on the rate in the rate column.

Is it possible to do it this way? Or should this be converted into the tiered level and use H-look up?

@West Man Thank you. I think your formula may work if I break it down into the tiered level data. I will try it out later today, and let you know if it's exactly what I was looking for.

Thank you both for your time and help!

Have a great day!

 

[West Man]

A slightly shorter version =(A2>=10000)*200+(MAX(0,(FLOOR(A2-10000,500)))*B2)

 

[Need_Excelhelp]

A slightly shorter version =(A2>=10000)*200+(MAX(0,(FLOOR(A2-10000,500)))*B2)

Thank you!

Sorry, I couldn't reply earlier.

I tried this formula, and tried couple of rebates manually to compare answers. This formula provides accurate answer for values less than $11000. But not after that. Maybe I was unclear in my question when I said for every additional $500 spent. For example, if the purchased amount is $11,100 and the additional rate of 1.5% then the rebate should be $222.50. =10000*.02+1000*.015. (1000 came from 2 $500 thresholds passed). Does it make sense?

But you are a genius! I have no idea how you came up with this formula. I don't even understand what you did. But it works for some them!

I have separated my data into different tiers and am thinking of changing the rebate plan so it's easier to calculate as the current idea seems almost impossible.

Once again, thank you so much for your help!

Regards,

KS

 

[West Man]

I tried the example you used above and it produces a rebate of 215 which is the correct result - not 225. Do some more testing and report back. The formula can be easily changed if needed. And I can explain its workings if it would be helpful to you.

 

[Need_Excelhelp]

I tried the example you used above and it produces a rebate of 215 which is the correct result - not 225. Do some more testing and report back. The formula can be easily changed if needed. And I can explain its workings if it would be helpful to you.

Hi,

I tried again, and it works!!!

I must have done something wrong yesterday when I was clearing some data and playing around with different numbers. Thank you so much for your help! Yes, if you could explain to me what you did then it would be great.

Also, I should probably make a separate thread for this, but do you if it's possible to generate random numbers by linking two columns and making sure that they are not repeated within a certain time frame?

Once again, thank you for your help!

Regards,

KS

 

[West Man]

=(A2>=10000)*200+(MAX(0,(FLOOR(A2-10000,500)))*B2)

The first part, (A2>=10000) compares A2 to 10,000 and returns either True or False. When this true/False is used in an arithmetical operation, it is coerced to 0 or 1 (0 being false). so when multiplied by 200 it results in either 0 or 200 depending on the value of A2.

FLOOR(A2-10000,500) simply rounds down A2-10000 to the nearest multiple of 500. Keep in mind that this could be a negative number if A2 is less than 10000.

The Max function is needed because of that possibility of a negative number and insures that B2 is either multiplied by 0 or a positive value of A2-10000.

In retrospect, I probably over thought it. This would suffice: IF(A2<=10000,0,200+FLOOR(A2-10000,500)*B2)