Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# Formula to calculate outcome based on number range

Posted on 2014-12-29
Medium Priority
166 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
Question by:recycleaus
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• Learn & ask questions
• 4
• 3
• 2
9 Comments

LVL 27

Expert Comment

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

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

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

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

LVL 27

Expert Comment

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

Rory Archibald earned 1000 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

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

Glenn Ray earned 1000 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

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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will diâ€¦
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 demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
###### Suggested Courses
Course of the Month11 days, 14 hours left to enroll

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

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