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