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

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

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.

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

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.

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)

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.

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.

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

Thank you for the feedback and rep .

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