# Formula to Locate Top 40% of a Value Column

1. ## 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.  Register To Reply

2. ## 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  Register To Reply

3. ## 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.  Register To Reply

4. ## 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  Register To Reply

5. ## 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:  `Please Login or Register  to view this content.`

Try it on unsorted data to test.  Register To Reply

6. ## 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)  Register To Reply

7. ## 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.  Register To Reply

8. ## 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.   Register To Reply

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

Thank you for the feedback and rep .  Register To Reply