finding top 80% line from a descending list
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. |
finding top 80% line from a descending list
PERCENTILE?
Użytkownik "Quester" napisał w wiadomości ... 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. |
finding top 80% line from a descending list
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. -- Gary''s Student - gsnu200849 "Quester" wrote: 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. |
finding top 80% line from a descending list
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. "Gary''s Student" wrote: 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. -- Gary''s Student - gsnu200849 "Quester" wrote: 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. |
All times are GMT +1. The time now is 06:16 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com