Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to sort out (ascending or descending) automatically a list | Excel Worksheet Functions | |||
how do i sort a set of averages and have them list in descending o | Excel Worksheet Functions | |||
Drop Down List is not in Descending Order | Excel Discussion (Misc queries) | |||
Excel List Sorting Problem (Descending) | Excel Discussion (Misc queries) | |||
how to create a dynamic descending list of months & years | Excel Discussion (Misc queries) |