Formula to calculate outcome based on number range
I have a range of numbers which I need to compare against a table to determine a scaled output. So, for example the table has 7 different options, less than 1000 (equals 1), between 1001-2000 (equals 2), between 2001-3000 (equals 3), etc and they relate to different rates for certain products.
When we input numbers, for example 2431, the output would automatically determine the first 1000 is to be multiplied by rate 1 for that product, then 1001-2000 is to be multiplied by rate 2 for that product and the final 431 is to be multiplied by rate 3 for that product.
I know its a lookup but I dont know how to do a range lookup for a scaled outcome like this. I could just do a huge IF formula but I know there is a better way.
I have attached the spreadsheet im working so it is clearer and perhaps the solution can just be put directly into the spreadsheet.
Thanks
Veolia-Pricing---TROY.xlsx
When we input numbers, for example 2431, the output would automatically determine the first 1000 is to be multiplied by rate 1 for that product, then 1001-2000 is to be multiplied by rate 2 for that product and the final 431 is to be multiplied by rate 3 for that product.
I know its a lookup but I dont know how to do a range lookup for a scaled outcome like this. I could just do a huge IF formula but I know there is a better way.
I have attached the spreadsheet im working so it is clearer and perhaps the solution can just be put directly into the spreadsheet.
Thanks
Veolia-Pricing---TROY.xlsx
ASKER
Right now its set up to calculate as if all the kgs are subject to (for example) rate 3 but I want to change it so its scaled and the first 1000kgs are subject to rate 1, the 2nd 1000kgs are subject to rate 2, and then the remaining 431kgs (based on the 2431kgs example above) are subject to rate 3.... does this make sense?
So, taking an example from the actual data in the workbook (rows 20 & 33):
The Total Kg is 3,250. This currently returns a State Rate of 3 and all component pricing is based on that Rate (row 8).
You want this changed so that:
1) The first 1,000 Kg is charged at Rate 1. This leaves 2,250 Kg
2) The next 1,000 Kg is charged at Rate 2. This leaves 1,250 Kg
3) The remaining 1,250 Kg is charged at Rate 3 (because the limit is 3,500 Kg).
How would this then be broken down into the individual components for pricing since their quantities do not follow this? Again, the current workbook does not price by component quantity; the rates are driven entirely by the Total Kg amount.
-Glenn
The Total Kg is 3,250. This currently returns a State Rate of 3 and all component pricing is based on that Rate (row 8).
You want this changed so that:
1) The first 1,000 Kg is charged at Rate 1. This leaves 2,250 Kg
2) The next 1,000 Kg is charged at Rate 2. This leaves 1,250 Kg
3) The remaining 1,250 Kg is charged at Rate 3 (because the limit is 3,500 Kg).
How would this then be broken down into the individual components for pricing since their quantities do not follow this? Again, the current workbook does not price by component quantity; the rates are driven entirely by the Total Kg amount.
-Glenn
But is the rate supposed to be determined by the total weight, and then that rate tier applies to each part, or should each part have a rate based on its own weight?
^This is my point, exactly. See the attached workbook for a comparison of the existing method - rate determined by Total Kg - versus rate determined by component Kg.
EE-Veolia-Pricing-TROY.xlsx
EE-Veolia-Pricing-TROY.xlsx
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Rory you've nailed it BUT it isn't calculating 100% correct, for example, D33 should equal 5900 not 5900.25 and D36 should be 8835 not 8835.35...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks to Rory and Glenn... exactly what I was after so appreciate your help and have a happy new year.
One thing that the workbook does not do is calculate rates for each component (tubes, globes, co-mingled). Currently, their rates are essentially controlled by the Total Kg total.
-Glenn