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
recycleausAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
Glenn RayExcel VBA DeveloperCommented:
The workbook appears to calculate the correct rates based on the "Total Kg" amounts in G20:G26.  Why would a value of 2,431 [kg] be calculated as any rate other than 3 (the rate for 2001-3500 kg)?

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
0
 
recycleausAuthor Commented:
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?
0
 
Glenn RayExcel VBA DeveloperCommented:
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
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Rory ArchibaldCommented:
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?
0
 
Glenn RayExcel VBA DeveloperCommented:
^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
0
 
Rory ArchibaldCommented:
If you wanted to do it based on Total weight, you'd have to work out the rate for each tier for the total weights, then pro rate that for each part (tube, Glob, Co-mingle). If you want to work out the rate for each part, it can be something like the attached.
Veolia-Pricing---TROY.xlsx
0
 
recycleausAuthor Commented:
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...
0
 
Glenn RayExcel VBA DeveloperCommented:
Rory's formulas are the right idea, but the breakpoints used in B6:B12 will not work.  They slightly shift the price at each breakpoint.

Instead, a separate range has to be set up and the formulas tweaked just slightly.  See the attached file.

-Glenn
EE-Veolia-Pricing-TROY.xlsx
0

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
 
recycleausAuthor Commented:
Thanks to Rory and Glenn... exactly what I was after so appreciate your help and have a happy new year.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.