Need help Average function

A

aljorge

Hi there,

I've been sitting here for ours now and I can't come across the solution:

I have a number of rows (names) with 1 number pr day (columns), and I want
to get an average of the 10 HIGHEST results in each row, ie when the a
given person has more 15 results, the 5 worse results should be excluded.
Any ideas?

(forgive me if it's a stupid question, I'm a newbie :))

Allan
 
B

Bob Phillips

Allan,

This formula will get you the average of the top ten, or how ever many there
are

=AVERAGE(LARGE(OFFSET(B22,,,1,COUNTA(B22:AM22)),ROW(INDIRECT("1:"&MIN(10,COU
NTA(B22:M22))))))

it is an array formula, so commit with Ctrl-Shift-Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
S

SteveG

Allan,

You could use this as well.

Where row 1 contains your headers, column A is your names, column B:p
are your days and you enter this in cell Q2.

{=AVERAGE(IF(B2:p2>=LARGE(B2:p2,10),B2:p2))}

This will give you the average of only the top ten values in row 2.
Change the # 10 in the LARGE formula to get different results i.e. top
5 change to 5 and so on. You can then drag the formula down for each
row you wish ti apply it to.

This is also an array formula so Ctrl-Shift-Enter applies as well.

Regards,

Steve
 
R

Ron Rosenfeld

Hi there,

I've been sitting here for ours now and I can't come across the solution:

I have a number of rows (names) with 1 number pr day (columns), and I want
to get an average of the 10 HIGHEST results in each row, ie when the a
given person has more 15 results, the 5 worse results should be excluded.
Any ideas?

(forgive me if it's a stupid question, I'm a newbie :))

Allan
Click to expand...


If your starts in B2 and extends as far right as necessary, then:


=SUMPRODUCT(LARGE(B2:IV2,ROW(INDIRECT("1:"
&MIN(10,COUNT(B2:IV2))))))/MIN(10,COUNT(B2:IV2))



--ron
 
A

aljorge

Allan,

This formula will get you the average of the top ten, or how ever many
there are

=AVERAGE(LARGE(OFFSET(B22,,,1,COUNTA(B22:AM22)),ROW(INDIRECT("1:"&MIN(1
0,COU NTA(B22:M22))))))

it is an array formula, so commit with Ctrl-Shift-Enter
Click to expand...

I'm sitting with a spanish excel and I have had some trouble finding the
equivalent functions (why do they translate them???) but it seems to be
right now - however, I must be doing something wrong since both your
proposal and SteveG's gives me the same result, the highest value in the
row!! And, just to make me more confused, Ron's gives me the same but
divided by 10!

I'll continue until I find it, but in any case, thx for your help

Allan
 
A

aljorge

Allan,

You could use this as well.

Where row 1 contains your headers, column A is your names, column B:p
are your days and you enter this in cell Q2.

{=AVERAGE(IF(B2:p2>=LARGE(B2:p2,10),B2:p2))}

This will give you the average of only the top ten values in row 2.
Change the # 10 in the LARGE formula to get different results i.e. top
5 change to 5 and so on. You can then drag the formula down for each
row you wish ti apply it to.

This is also an array formula so Ctrl-Shift-Enter applies as well.

Regards,

Steve
Click to expand...

Please see my answer to Bob Phillips - thx for your help
 
A

aljorge

If your starts in B2 and extends as far right as necessary, then:


=SUMPRODUCT(LARGE(B2:IV2,ROW(INDIRECT("1:"
&MIN(10,COUNT(B2:IV2))))))/MIN(10,COUNT(B2:IV2))



--ron
Click to expand...

Please see my answer to Bob Phillips - thx for your help
 
B

Bob Phillips

I think that should be

=PROMEDIO(K.ESIMO.MAYOR(OFFSET(B22;;;1;CONTAR(B22:AM22));
ROW(INDIRECTO("1:"&FILA(10;CONTAR(B22:M22))))))

in Spanish

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
S

SteveG

Allan,

In Bob's I think the OFFSET should be DESREF

COUNTA = CONTARA

ROW = FILA

MIN = MIN

I'm just going off the site I gave you the link to.


Steve
 
A

aljorge

Allan,

In Bob's I think the OFFSET should be DESREF

COUNTA = CONTARA

ROW = FILA

MIN = MIN

I'm just going off the site I gave you the link to.


Steve
Click to expand...

Yes, that was it - I had them all correct except LARGE = K.ESIMO.MAYOR!

Thx a lot, you've been very help full

Allan
 
A

aljorge

I think that should be

=PROMEDIO(K.ESIMO.MAYOR(OFFSET(B22;;;1;CONTAR(B22:AM22));
ROW(INDIRECTO("1:"&FILA(10;CONTAR(B22:M22))))))

in Spanish
Click to expand...

as Steve says below, OFFSET should be DESREF, else everything correct and
working - Thx a lot!

Allan
 
Top