Finding multiple values of a function

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

trainerbro - 03 Nov 2006 20:28 GMT

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

Reply to this Message

Barb Reinhardt - 03 Nov 2006 21:11 GMT

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

> 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

Reply to this Message

trainerbro - 06 Nov 2006 19:56 GMT

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

> I think I might be able to help here.
>
[quoted text clipped - 14 lines]
> > cannot figureo out how to do it without including the same values. Thank you
> > in advance for any help you can provide

Reply to this Message

Barb Reinhardt - 06 Nov 2006 20:09 GMT

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

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

> 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
[quoted text clipped - 18 lines]
> > > cannot figureo out how to do it without including the same values. Thank you
> > > in advance for any help you can provide

Reply to this Message