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!

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!

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!