Solved

Formula to calculate outcome based on number range

Posted on 2014-12-29
9
157 Views
Last Modified: 2014-12-30
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
0
Comment
Question by:recycleaus
  • 4
  • 3
  • 2
9 Comments
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40521996
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
 

Author Comment

by:recycleaus
ID: 40522002
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
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40522030
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 40522035
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40522044
^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
 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 250 total points
ID: 40522072
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
 

Author Comment

by:recycleaus
ID: 40522087
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
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 250 total points
ID: 40522717
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
 

Author Closing Comment

by:recycleaus
ID: 40523479
Thanks to Rory and Glenn... exactly what I was after so appreciate your help and have a happy new year.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now