Excel 2007 : Which Price Range does Value fall within?

**Metzed** · 10-26-2010, 07:15 AM

I have attached a screenshot of my worksheet.

I have a range of prices in Column A. Column B cells need to display the percentage of the column A value, where the percentage is based on which price range (eg D2:E2) the Column A value lies within.

eg.
A2 = 150
B2 = 22.5 (because 150 lies within the range of 0-1499 and is assigned a percentage value of 15%. 22.5 is 15% of 150)

D2 = 0
E2 = 1499
F2 = 15

Hope someone can tell me what formula I need to generate the Column B values, because unfortunately I have no idea.

Thanks

**DonkeyOte** · 10-26-2010, 07:20 AM

Based on the screen shot

Please Login or Register  to view this content.

**Metzed** · 10-26-2010, 07:38 AM

Excellent, thanks a lot!

If the value that we get in Column B is below 250 I want to display 250 instead.

I've tried this and it works, but is there a better method to learn?

Please Login or Register  to view this content.

**DonkeyOte** · 10-26-2010, 07:48 AM

Better (more efficient) to use MAX:

Please Login or Register  to view this content.

that will return the greater of the two values

**Metzed** · 10-26-2010, 08:01 AM

Great to know, thanks very much for your help.

**Metzed** · 10-26-2010, 09:26 AM

Hi, sorry just something else that I can't work out.

Naturally when dealing with price bands like this, the first few values in Column A which enter the next price band, can drop in value below the preceding value. And so I'm wondering if there is a way to set a minimum price for each price band. A bit like we did with MAX, but automated for each price band.

In the screenshot you can see that I've added another column to the lookup table which displays the minimum price to display for that band. Is there a way to use one formula for all cells, but take into account the minimum price for each band?

Sorry about the column title: "Minimum Percentage of Price" - it's not immediately clear, I didn't know what else to call it. Hopefully you understand.

Thanks

**DonkeyOte** · 10-26-2010, 09:32 AM

Do you mean:

Please Login or Register  to view this content.

On an aside it's better to post files than images... people won't be inclined to recreate your image in XL form.

**Metzed** · 10-26-2010, 09:54 AM

Yes great, I've checked it against data which I've created the longer away and it's all correct. Superb.

I take your point regarding screenshots, makes complete sense, thanks for the tip.

I wish this forum had some kind of Thanks rating......anyway Thanks!

**DonkeyOte** · 10-26-2010, 10:00 AM

Originally Posted by Metzed

I wish this forum had some kind of Thanks rating

Going forward you can use the "scales" icon above the atop the post in question and provide feedback - no need to in this instance however - a thanks is enough.