#1
|
|||
|
|||
Formula that will use different rates depending on the value entered
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. |
#2
|
|||
|
|||
=SUMPRODUCT(1*(A1>({0,5000,10000})),(A1-({0,5000,10000})),({1.6,-0.4,-0.2}))
where A1 holds the amount entered. |
#3
|
||||
|
||||
This is one way =MIN(A1,5000)*1.6+MIN(A1-5000,5000)*1.2*(A1>5000)+(A1-10000)*(A1>10000)
__________________
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 |
#4
|
|||
|
|||
Quote:
Well it worked - thank you very much But, I don't understand it. |
#5
|
||||
|
||||
The differential rates technique is explained at http://www.mcgimpsey.com/excel/variablerate1.html
__________________
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 |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Marking specific cells depending on start and end time entered | ellebb85 | Excel Programming | 0 | 01-17-2016 02:50 AM |
different rates on sunday | pascalbidouille | Project | 1 | 07-01-2015 01:58 PM |
How to create a table with a number of line depending a number entered by user | Cellendhyll | Word Tables | 3 | 07-10-2014 05:49 AM |
Viewing Resource Rates | decker | Project | 6 | 02-07-2014 03:23 PM |
Formula to select description when code is entered | Natasha | Excel | 1 | 09-25-2011 12:59 PM |