I am looking to create a formula that will allow me to calculate my example below:

First 5,000*1.6


Next 5,000*1.2
Over 10,000*1

For example the Limit entered was 15,000 the formula would have to do something like this.

5000*1.6 = 8000
5000*1.2 = 6000
5000*1 = 5000
=8000+6000+5000
=19,000

I would imagine I need to use IF statements but I just can't seem to get the result I need.

=SUMPRODUCT(1*(A1>({0,5000,10000})),(A1-({0,5000,10000})),({1.6,-0.4,-0.2}))

where A1 holds the amount entered.

This is one way =MIN(A1,5000)*1.6+MIN(A1-5000,5000)*1.2*(A1>5000)+(A1-10000)*(A1>10000)

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

-------

Well it worked - thank you very much

But, I don't understand it.

The differential rates technique is explained at http://www.mcgimpsey.com/excel/variablerate1.html

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post