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!

 

[Need_Excelhelp]

=(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)

Thank you! Yeah, the IF function is much simpler and easy to understand, but I would have never got it on my own because the "Floor function" is very new to me.

Thank you once again.

Have a great weekend!