I am trying to find the Average of the lower 10% of a column of data. I
cannot figureo out how to do it without including the same values. Thank you
in advance for any help you can provide

I think I might be able to help here.

Let's say your data is in A1:A100
Add a column B to determine what the rank of your entries.
=RANK(A1,A$1:A$100,1)
Determine the # of entries
=COUNT(A$1:A$100)
Determine the highest rank for the bottom 10%
=ROUND(COUNT(A$1:A$100)*.1,0)


Enter this:

=AVERAGE(IF(B1:B100<=ROUND(0.1*COUNT(A1:A100),0),A 1:A100))
COMMIT with CTRL SHIFT ENTER




"trainerbro" wrote:

I am trying to find the Average of the lower 10% of a column of data. I
cannot figureo out how to do it without including the same values. Thank you
in advance for any help you can provide

How can I designate the rankings if I have a variable amount of cells each
time. Would it be easiest to make the entire column a ranking column and
then just write the function to exclude the Errors? Thanks again

"Barb Reinhardt" wrote:

I think I might be able to help here.

Let's say your data is in A1:A100
Add a column B to determine what the rank of your entries.
=RANK(A1,A$1:A$100,1)
Determine the # of entries
=COUNT(A$1:A$100)
Determine the highest rank for the bottom 10%
=ROUND(COUNT(A$1:A$100)*.1,0)


Enter this:

=AVERAGE(IF(B1:B100<=ROUND(0.1*COUNT(A1:A100),0),A 1:A100))
COMMIT with CTRL SHIFT ENTER




"trainerbro" wrote:

I am trying to find the Average of the lower 10% of a column of data. I
cannot figureo out how to do it without including the same values. Thank you
in advance for any help you can provide

You could create a named dynamic range for column A and B (for example).

http://www.ozgrid.com/Excel/DynamicRanges.htm


"trainerbro" wrote:

How can I designate the rankings if I have a variable amount of cells each
time. Would it be easiest to make the entire column a ranking column and
then just write the function to exclude the Errors? Thanks again

"Barb Reinhardt" wrote:

I think I might be able to help here.

Let's say your data is in A1:A100
Add a column B to determine what the rank of your entries.
=RANK(A1,A$1:A$100,1)
Determine the # of entries
=COUNT(A$1:A$100)
Determine the highest rank for the bottom 10%
=ROUND(COUNT(A$1:A$100)*.1,0)


Enter this:

=AVERAGE(IF(B1:B100<=ROUND(0.1*COUNT(A1:A100),0),A 1:A100))
COMMIT with CTRL SHIFT ENTER




"trainerbro" wrote:

I am trying to find the Average of the lower 10% of a column of data. I
cannot figureo out how to do it without including the same values. Thank you
in advance for any help you can provide