Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Top 10% and Bottom 10%
I have a set of numbers. I want Excel to tell me which numbers are in the
top 10% and which numbers are in the bottom 10%. For example, say the numbers a 45 23 26 21 5 10 32 11 Which of those numbers are in the top 10% and which are in the bottom 10%? The values of the numbers will change, so I want to create a formula that will work on the numbers. Thanks! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Top 10% and Bottom 10%
Your problem statement is a bit ambiguous. Do you mean the top 10% of
the range of values (e.g., the range is 5-45, so 10% of the range is 4, so the top 10% would be values greater than or equal to 45-4, or 41, and the bottom 10% would be values less than or equal to 9) or the top 10% of the *number* of values (you have only 8 values, so the top 10% would be 0.8 values, presumably rounded up to 1). If the former, one way: =COUNTIF(A1:A8,"=" & MIN(A1:A8)+(MAX(A1:A8)-MIN(A1:A8))*90%) and =COUNTIF(A1:A8,"<=" & MIN(A1:A8)+(MAX(A1:A8)-MIN(A1:A8))*10%) In article , Joshann wrote: I have a set of numbers. I want Excel to tell me which numbers are in the top 10% and which numbers are in the bottom 10%. For example, say the numbers a 45 23 26 21 5 10 32 11 Which of those numbers are in the top 10% and which are in the bottom 10%? The values of the numbers will change, so I want to create a formula that will work on the numbers. Thanks! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Top 10% and Bottom 10%
Sorry. I meant the top 10% of the range of values. I will try your
suggestion. That looks pretty simple. I thought I would have to get the standard deviation and then do something with that, so this looks much easier. "JE McGimpsey" wrote: Your problem statement is a bit ambiguous. Do you mean the top 10% of the range of values (e.g., the range is 5-45, so 10% of the range is 4, so the top 10% would be values greater than or equal to 45-4, or 41, and the bottom 10% would be values less than or equal to 9) or the top 10% of the *number* of values (you have only 8 values, so the top 10% would be 0.8 values, presumably rounded up to 1). If the former, one way: =COUNTIF(A1:A8,"=" & MIN(A1:A8)+(MAX(A1:A8)-MIN(A1:A8))*90%) and =COUNTIF(A1:A8,"<=" & MIN(A1:A8)+(MAX(A1:A8)-MIN(A1:A8))*10%) In article , Joshann wrote: I have a set of numbers. I want Excel to tell me which numbers are in the top 10% and which numbers are in the bottom 10%. For example, say the numbers a 45 23 26 21 5 10 32 11 Which of those numbers are in the top 10% and which are in the bottom 10%? The values of the numbers will change, so I want to create a formula that will work on the numbers. Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
why doesn't Excel 2003 bottom double border icon work | Excel Discussion (Misc queries) | |||
dislike jump bottom of column by double-clicking the bottom of cel | Excel Discussion (Misc queries) | |||
Footer does not print on bottom of document | Excel Discussion (Misc queries) | |||
Rows to repeat at BOTTOM. | Setting up and Configuration of Excel | |||
how to print a row on the bottom of every page | Excel Discussion (Misc queries) |