Excel 2007 : Which Price Range does Value fall within?

1. Which Price Range does Value fall within?

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

2. Re: Which Price Range does Value fall within?

Based on the screen shot

``Please Login or Register  to view this content.``

3. Re: Which Price Range does Value fall within?

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

4. Re: Which Price Range does Value fall within?

Better (more efficient) to use MAX:

``Please Login or Register  to view this content.``
that will return the greater of the two values

5. Re: Which Price Range does Value fall within?

Great to know, thanks very much for your help.

6. Re: Which Price Range does Value fall within?

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

7. Re: Which Price Range does Value fall within?

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.

8. Re: Which Price Range does Value fall within?

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!

9. Re: Which Price Range does Value fall within?

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.

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1