Finding multiple values of a function

T

trainerbro

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
 
B

Barb Reinhardt

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),A1:A100))
COMMIT with CTRL SHIFT ENTER
 
T

trainerbro

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
 
Top