Need expert helpâ€”fast? Use the Help Bell for personalized assistance getting answers to your important questions.

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

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

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

EE-Veolia-Pricing-TROY.xlsx

Veolia-Pricing---TROY.xlsx

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.

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