Average of top 3 numbers.

[Guest]

Hi, im making a worksheet for our shooting results. I need the average of the
top 3 results from collum E6 to W6 and top 3 reults from Y6 to AJ6. Can
anyone help me with this one??

 

[Don Guillett]

does this help
=SUM(LARGE(E1:E14,{1,2,3}))/3

 

[Don Guillett]

or even

=AVERAGE(LARGE($E$1:$E$14,{1,2,3}))

 

[Guest]

I just get error when i try it...

 

[Arvi Laanemets]

Hi

Maybe
=AVERAGE(LARGE(E1:E14,{1;2;3}))

Arvi Laanemets

 

[Don Guillett]

perhaps you didn't change from my EXAMPLE. Try this.
=AVERAGE(LARGE($E$6:$W$6,{1,2,3}))

 

[Gord Dibben]

=AVERAGE(LARGE(E6:W6,{1,2,3}))

Same for other range.


Gord Dibben Excel MVP

 

[Guest]

Still error, hm.....

 

[Peo Sjoblom]

What kind of error?

--
Regards,

Peo Sjoblom

(No private emails please)

 

[Guest]

Just a thought here...

If you are typing the formula, instead of copy/paste, did you notice the
curly braces in the formula?

=AVERAGE(LARGE(E6:W6,{1,2,3}))
The character before the 1 is on the [ key (you hold down Shift to get it).
The character after the 3 is on the ] key (again, hold down Shift to get it).

Does that help?

***********
Regards,
Ron

 

[David McRitchie]

Is everthing a number. test with =isnumber(a1)

 

[Arvi Laanemets]

Hi

What is array delimiter in window's International settings for your comp?
Maybe semicolon! (Check a version of Don's formula I posted yesterday!)

 

[Guest]

Tried to type it in but still error. It popps up a window with: The formula
you typed contains a error". Can i send you the excel sheet so you can try it?

Ron Coderre skrev:

Just a thought here...

If you are typing the formula, instead of copy/paste, did you notice the
curly braces in the formula?

=AVERAGE(LARGE(E6:W6,{1,2,3}))
The character before the 1 is on the [ key (you hold down Shift to get it).
The character after the 3 is on the ] key (again, hold down Shift to get it).

Does that help?

***********
Regards,
Ron

Still error, hm.....

 

[Guest]

Everything is numbers yes.

David McRitchie skrev:

 

[Guest]

Sorry was a bit early. When i mark the whole area i get FALSE.

 

[Guest]

Nah...don't send the worksheet...

Just copy the below formula
(paint through it using your mouse then right click and select Copy)
=AVERAGE(LARGE(E6:W6,{1,2,3}))

Then, click on any cell on your worksheet that is NOT in row 6.
Right click and select Paste.

Does that help?

***********
Regards,
Ron

Tried to type it in but still error. It popps up a window with: The formula
you typed contains a error". Can i send you the excel sheet so you can try it?

Ron Coderre skrev:

Just a thought here...

If you are typing the formula, instead of copy/paste, did you notice the
curly braces in the formula?

=AVERAGE(LARGE(E6:W6,{1,2,3}))
The character before the 1 is on the [ key (you hold down Shift to get it).
The character after the 3 is on the ] key (again, hold down Shift to get it).

Does that help?

***********
Regards,
Ron

Still error, hm.....

 

[Guest]

Nope, when the message pop up and i press enter, "W6," in the formula is
marked...

Ron Coderre skrev:

 

[David McRitchie]

and so have you then found the problem and solution
was actually expecting you to use a helper column checking individual cells.

Or select the entire column and use GoTo (ctrl+g -or- Edit, goto), special
then either constants or formulas, and then
numbers

 

[Guest]

OK....The problem must be with your workbook, so...just to test:
Copy/Paste the formula into a NEW, BLANK worksheet
Then put some numbers in the E6:W6 range.

=AVERAGE(LARGE(E6:W6,{1,2,3}))

Does it work in that situation?

***********
Regards,
Ron

 

[Peo Sjoblom]

Are you using a different delimiter, if you are from Scandinavia (I noticed
a Swedish word there) there the delimiters are ; and not , so it would look
like

=MEDEL(STÖRSTA(E6:W6;{1\2\3}))


--

Regards,

Peo Sjoblom