finding top 80% line from a descending list

  • Thread starter Thread starter Quester
  • Start date Start date
Q

Quester

Similar to the top 10 function, but need it to be expressed in %, say top 80%
of the data population. Is there a simple way to do this? Many thanks.
 
There are two possible answers:

1

If you have 100 scores in A1 thru A100 that are in descend order, then the
top 80% will always be in cells A1 thru A80 (assuming no duplicated scores).

2

If you have scores in column A that are in descending order and want to know
what set of cells contributes 80% to the total, then in B1, enter:

=SUM($A$1:A1)/SUM(A:A) and copy down. For example:

12 40.00%
9 70.00%
3 80.00%
3 90.00%
2 96.67%
1 100.00%

Clearly the first three items give an 80% contribution.
 
hi Gary's Student,

Thanks for your answers. Your scenario 2 hits my point.
I'd like to know further if there's more direct solution that:
i) what if the numbers in column A is not in descending order; and
ii) I want to find the col A value that hits 80% line directly without
column B's listing.
 
Back
Top