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
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)
"aljorge" <el_oelhund@yahoo.es> wrote in message
news:Xns9712B3550E4Dyoqueseyoquesecom@213.0.184.82...
> 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
>
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
On Fri, 18 Nov 2005 16:37:47 +0000 (UTC), aljorge <el_oelhund@yahoo.es> wrote:
>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
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
"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in
news:O6EePBG7FHA.3456@TK2MSFTNGP10.phx.gbl:
> 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
>
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
SteveG <SteveG.1ypdfm_1132334700.8499@excelforum-nospam.com> wrote in
news:SteveG.1ypdfm_1132334700.8499@excelforum-nospam.com:
>
> 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
>
>
Please see my answer to Bob Phillips - thx for your help
Ron Rosenfeld <ronrosenfeld@nospam.org> wrote in
news:8j3sn1l7igvq0jt3vjj805qcfu8jlktrmc@4ax.com:
> On Fri, 18 Nov 2005 16:37:47 +0000 (UTC), aljorge
> <el_oelhund@yahoo.es> wrote:
>
>>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
>
>
> 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
Please see my answer to Bob Phillips - thx for your help
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)
"aljorge" <el_oelhund@yahoo.es> wrote in message
news:Xns9712CE2561425yoqueseyoquesecom@213.0.184.82...
> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in
> news:O6EePBG7FHA.3456@TK2MSFTNGP10.phx.gbl:
>
> > 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
> >
>
> 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
Allan,
I believe that the Spanish equivalents are as follows. I don't have it installed on my pc but I got this off of:
http://cherbe.free.fr/traduc_fonctions_xl97.html
AVERAGE = PROMEDIO
LARGE = K.ESIMO.MAYOR
Hope this helps.
Steve
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
SteveG <SteveG.1yplay_1132344900.9229@excelforum-nospam.com> wrote in
news:SteveG.1yplay_1132344900.9229@excelforum-nospam.com:
>
> 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
>
>
Yes, that was it - I had them all correct except LARGE = K.ESIMO.MAYOR!
Thx a lot, you've been very help full
Allan
"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in
news:uYwhpoH7FHA.3876@TK2MSFTNGP09.phx.gbl:
> 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
>
as Steve says below, OFFSET should be DESREF, else everything correct and
working - Thx a lot!
Allan
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks