+ Reply to Thread
Results 1 to 9 of 9

Formula to return value for top, middle, bottom % values

  1. #1
    Registered User
    Join Date
    05-15-2013
    Location
    Omaha, Nebraska
    MS-Off Ver
    2007
    Posts
    23

    Question Formula to return value for top, middle, bottom % values

    Hi,

    I need to create a formula to return values(1,2 or 3,) dependent on the top, middle and bottom percentage range in another column. So if the dependent cell value is 97.25% is should return 3, if its 10.1% return 1, etc(Max points for be in top 25%)

    Any ideas? This novice is stumped

    Thanks.

  2. #2
    Valued Forum Contributor Melvinrobb's Avatar
    Join Date
    06-19-2012
    Location
    Manitoba, Canada
    MS-Off Ver
    Excel 2013
    Posts
    1,128

    Re: Formula to return value for top, middle, bottom % values

    Assuming your range is A1:A10, does this formula work?
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    You will need to change the rank portion to the following:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by Melvinrobb; 06-13-2013 at 11:48 AM.
    Please click the * icon below if I have helped.

  3. #3
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Formula to return value for top, middle, bottom % values

    what is the actual split?
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  4. #4
    Registered User
    Join Date
    05-15-2013
    Location
    Omaha, Nebraska
    MS-Off Ver
    2007
    Posts
    23

    Re: Formula to return value for top, middle, bottom % values

    The split would be - if the cell E1 is in the top 25% of the Range E1:E25 return 3, if E1 is in the bottom 25% range of E1:E25 return 1, everything thing else (middle 50%) return 2 Column E is a percentage, could range from 1% to 100%

  5. #5
    Valued Forum Contributor Melvinrobb's Avatar
    Join Date
    06-19-2012
    Location
    Manitoba, Canada
    MS-Off Ver
    Excel 2013
    Posts
    1,128

    Re: Formula to return value for top, middle, bottom % values

    The attached file should work. Change the % in column G as desired.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    05-15-2013
    Location
    Omaha, Nebraska
    MS-Off Ver
    2007
    Posts
    23

    Re: Formula to return value for top, middle, bottom % values

    thanks, this might work but here's my question. if the values in E range from 10.6% to 89.6% will this compute the top, middle, bottom percentages of the range?

  7. #7
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Formula to return value for top, middle, bottom % values

    trouble with your split is 25 doesnt split 1/2/1

  8. #8
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Formula to return value for top, middle, bottom % values

    so lets assume the split is 6/13/6
    =IF(E1<=SMALL($E$1:$E$25,6),1,IF(E1>=LARGE($E$1:$E$25,6),3,2))

  9. #9
    Valued Forum Contributor Melvinrobb's Avatar
    Join Date
    06-19-2012
    Location
    Manitoba, Canada
    MS-Off Ver
    Excel 2013
    Posts
    1,128

    Re: Formula to return value for top, middle, bottom % values

    The file I attached will adjust dependant on the values in column B. If you put 100 entries in column B, no matter what the value range is, 25 entries will show 1, 50 entries will show 2, and 25 entries will show 3 (based on the percentages listed in column G).

+ 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