Solved

Formula to calculate outcome based on number range

Posted on 2014-12-29
9
161 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
[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

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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
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
 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

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;…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

734 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