+ Reply to Thread
Results 1 to 9 of 9

Excel 2007 : Which Price Range does Value fall within?

  1. #1
    Registered User
    Join Date
    10-26-2010
    Location
    UK
    MS-Off Ver
    Excel 2007
    Posts
    10

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

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Which Price Range does Value fall within?

    Based on the screen shot

    Please Login or Register  to view this content.
    Last edited by DonkeyOte; 10-26-2010 at 07:26 AM. Reason: missing $ ... schoolboy error !

  3. #3
    Registered User
    Join Date
    10-26-2010
    Location
    UK
    MS-Off Ver
    Excel 2007
    Posts
    10

    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. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    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. #5
    Registered User
    Join Date
    10-26-2010
    Location
    UK
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Which Price Range does Value fall within?

    Great to know, thanks very much for your help.

  6. #6
    Registered User
    Join Date
    10-26-2010
    Location
    UK
    MS-Off Ver
    Excel 2007
    Posts
    10

    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
    Attached Images Attached Images
    Last edited by Metzed; 10-26-2010 at 09:30 AM.

  7. #7
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    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. #8
    Registered User
    Join Date
    10-26-2010
    Location
    UK
    MS-Off Ver
    Excel 2007
    Posts
    10

    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. #9
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Which Price Range does Value fall within?

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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