+ Reply to Thread
Results 1 to 9 of 9

Formula to Locate Top 40% of a Value Column

  1. #1
    Registered User
    Join Date
    01-14-2019
    Location
    Oklahoma
    MS-Off Ver
    Excel 2016
    Posts
    16

    Formula to Locate Top 40% of a Value Column

    I may not be wording this correctly, so am attaching a document to show illustrate. I have a column of values and I want to use a formula to identify the items that make up the top 40% of the total of the column. In the document I summed the column, calculated 40% of this, sorted high to low, then highlighted the items that would fall into this category. These are the items I want separated out (locate). If you sum these items you see they are just under the 40% of the total.

    I also have a column with a percentile formula added, but it is not calculating what I want. On a very basic example the percentile function worked, so I left it there, but after using the actual figures, I'm thinking I need a different kind of formula. Any help is appreciated.
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    09-19-2018
    Location
    USA
    MS-Off Ver
    2016
    Posts
    208

    Re: Formula to Locate Top 40% of a Value Column

    You can probably use this formula in C7 and filled downward:

    =SUM($B$7:B7)<=$C$3

  3. #3
    Registered User
    Join Date
    01-14-2019
    Location
    Oklahoma
    MS-Off Ver
    Excel 2016
    Posts
    16

    Re: Formula to Locate Top 40% of a Value Column

    That works when sorted high to low, but I am looking for a formula that does not rely on it being sorted in a certain way. This will be used by other people, so if they decide they want to sort by name instead of value it would change this completely. Thank you though, that was an easy solution if it were just for me.

  4. #4
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO200;Win10/MSO2016
    Posts
    10,375

    Re: Formula to Locate Top 40% of a Value Column

    Maybe:
    In C3:
    Please Login or Register  to view this content.
    , where B3 holds the desired %
    In C4:
    Please Login or Register  to view this content.
    , locate the row if the corresponding C3 value
    D4:
    Please Login or Register  to view this content.
    , Just wondering...
    C7:C74 (Return values wrt desired percentile) :
    Please Login or Register  to view this content.
    *

    *CSE formula
    Attached Files Attached Files
    Ben Van Johnson

  5. #5
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, 365
    Posts
    10,344

    Re: Formula to Locate Top 40% of a Value Column

    If I understand correctly this works regardless of sort order. Sorting is done "in formula". Much of this can be simplified if helper columns are allowed. BTW: it doesn't appear that percentile should be an issue. What am I missing?

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Try it on unsorted data to test.
    Last edited by FlameRetired; 02-12-2019 at 12:01 AM.
    Dave

  6. #6
    Forum Expert
    Join Date
    07-20-2011
    Location
    mysore
    MS-Off Ver
    Excel 2007
    Posts
    4,555

    Re: Formula to Locate Top 40% of a Value Column

    Two formulas are used
    In Column D

    =IF(SUMPRODUCT([Value]*([Value]>=[@Value]))>=$C$3,"",$B7)

    In column I

    =IF(SUMPRODUCT(($H$7:$H$74>=H7)*($H$7:$H$74))>=$C$3,"",$H7)
    Attached Files Attached Files
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  7. #7
    Registered User
    Join Date
    01-14-2019
    Location
    Oklahoma
    MS-Off Ver
    Excel 2016
    Posts
    16

    Re: Formula to Locate Top 40% of a Value Column

    Flame and kvs, both of your solutions are exactly what I needed. Proton your's held some useful tools that I may try to use in the future, but the percentile function doesn't pull the values I'm trying to get. Thank you everyone. I have marked this problem solved.

  8. #8
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, 365
    Posts
    10,344

    Re: Formula to Locate Top 40% of a Value Column

    You are welcome. Glad to help.

    Thank you for the feedback, rep and marking this thread Solved.

  9. #9
    Forum Expert
    Join Date
    07-20-2011
    Location
    mysore
    MS-Off Ver
    Excel 2007
    Posts
    4,555

    Re: Formula to Locate Top 40% of a Value Column

    Thank you for the feedback and rep .

+ 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