Average of top 3 numbers.

[Belzbub]

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
SalesAid Software
donaldb@281.com
"Belzbub" <Belzbub@discussions.microsoft.com> wrote in message
news:761625F9-9AB4-4B29-A37A-1EA9600ED61B@microsoft.com...
> 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]

or even

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

--
Don Guillett
SalesAid Software
donaldb@281.com
"Belzbub" <Belzbub@discussions.microsoft.com> wrote in message
news:761625F9-9AB4-4B29-A37A-1EA9600ED61B@microsoft.com...
> 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??

[Belzbub]

I just get error when i try it...

[Arvi Laanemets]

Hi

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

Arvi Laanemets


"Don Guillett" <donaldb@281.com> wrote in message
news:uZEy6S48FHA.2036@TK2MSFTNGP14.phx.gbl...
> does this help
> =SUM(LARGE(E1:E14,{1,2,3}))/3
>
> --
> Don Guillett
> SalesAid Software
> donaldb@281.com
> "Belzbub" <Belzbub@discussions.microsoft.com> wrote in message
> news:761625F9-9AB4-4B29-A37A-1EA9600ED61B@microsoft.com...
> > 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]

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

--
Don Guillett
SalesAid Software
donaldb@281.com
"Belzbub" <Belzbub@discussions.microsoft.com> wrote in message
news:FAB5FB64-E0F2-4285-9B1B-E9BA0B201E01@microsoft.com...
>I just get error when i try it...

[Gord Dibben]

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

Same for other range.


Gord Dibben Excel MVP


On Sun, 27 Nov 2005 10:50:09 -0800, "Belzbub"
<Belzbub@discussions.microsoft.com> wrote:

>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??

[Belzbub]

Still error, hm.....

[Peo Sjoblom]

What kind of error?

--
Regards,

Peo Sjoblom

(No private emails please)


"Belzbub" <Belzbub@discussions.microsoft.com> wrote in message
news:D96D1A20-01E0-4092-9BBD-1EFD72B110A8@microsoft.com...
> Still error, hm.....

[Ron Coderre]

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


"Belzbub" wrote:

> Still error, hm.....

[David McRitchie]

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

"Belzbub" <Belzbub@discussions.microsoft.com> wrote in message news:D96D1A20-01E0-4092-9BBD-1EFD72B110A8@microsoft.com...
> Still error, hm.....

[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!)


--
Arvi Laanemets
( My real mail address: arvil<at>tarkon.ee )


"Belzbub" <Belzbub@discussions.microsoft.com> wrote in message
news:D96D1A20-01E0-4092-9BBD-1EFD72B110A8@microsoft.com...
> Still error, hm.....

[Belzbub]

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
>
>
> "Belzbub" wrote:
>
> > Still error, hm.....

[Belzbub]

Everything is numbers yes.

David McRitchie skrev:

> Is everthing a number. test with =isnumber(a1)
>
> "Belzbub" <Belzbub@discussions.microsoft.com> wrote in message news:D96D1A20-01E0-4092-9BBD-1EFD72B110A8@microsoft.com...
> > Still error, hm.....
>
>
>

[Belzbub]

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


> Everything is numbers yes.

[Ron Coderre]

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


"Belzbub" wrote:

> 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
> >
> >
> > "Belzbub" wrote:
> >
> > > Still error, hm.....

[Belzbub]

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

Ron Coderre skrev:

> 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
>

[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

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Belzbub" <Belzbub@discussions.microsoft.com> wrote in message news:6A4540CF-6BF9-4955-8D8A-25992DDCF675@microsoft.com...
> Sorry was a bit early. When i mark the whole area i get FALSE.
>
>
> > Everything is numbers yes.
>

[Ron Coderre]

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


"Belzbub" wrote:

> Nope, when the message pop up and i press enter, "W6," in the formula is
> marked...
>
> Ron Coderre skrev:
>
> > 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
> >

[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



"Belzbub" <Belzbub@discussions.microsoft.com> wrote in message
news:6A4540CF-6BF9-4955-8D8A-25992DDCF675@microsoft.com...
> Sorry was a bit early. When i mark the whole area i get FALSE.
>
>
> > Everything is numbers yes.
>

[Belzbub]

Still the same, what can be the problem??

[Belzbub]

Tried it now but it doesent help. And i have english version.

David McRitchie skrev:

> 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

[Ron Coderre]

Peo Sjoblom asks an interesting question. What international settings are
you using? Perhaps your delimiters are NOT the same as the ones we're sending
you.

Try using different delimters.
Perhaps semicolons: =AVERAGE(LARGE(E6:W6,{1;2;3}))

or maybe this will work:
=AVERAGE(LARGE(E6:W6,ROW(1:3)))
Note: commit that array formula by holding down [Ctrl]+[Shift] when you
press [Enter].

Does that help?

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


"Belzbub" wrote:

> Still the same, what can be the problem??

[Belzbub]

Still the same. I have put the Excel file on my homepage:
http://home.no.net/belzbub/Bedriftsskyting/index.htm and go to
"stevneoversikt".

Belzbub

Ron Coderre skrev:

> Peo Sjoblom asks an interesting question. What international settings are
> you using? Perhaps your delimiters are NOT the same as the ones we're sending
> you.
>
> Try using different delimters.
> Perhaps semicolons: =AVERAGE(LARGE(E6:W6,{1;2;3}))
>
> or maybe this will work:
> =AVERAGE(LARGE(E6:W6,ROW(1:3)))
> Note: commit that array formula by holding down [Ctrl]+[Shift] when you
> press [Enter].
>
> Does that help?
>
> ***********
> Regards,
> Ron
>
>
> "Belzbub" wrote:
>
> > Still the same, what can be the problem??

[Ron Coderre]

First, I had no problem putting the formula into your worksheet.

Second, In your spreadsheet, you have less than 3 numbers in the range
E6:W6 causing the formula to return #NUM!. When I entered a third number, the
formula calculated properly.

I changed the formula to this to trap the "less than 3 numbers" situation:
=IF(COUNT(E6:W6)<3,"Less than 3 numbers",AVERAGE(LARGE(E6:W6,{1,2,3})))

Does that help?

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


"Belzbub" wrote:

> Still the same. I have put the Excel file on my homepage:
> http://home.no.net/belzbub/Bedriftsskyting/index.htm and go to
> "stevneoversikt".
>
> Belzbub
>
> Ron Coderre skrev:
>
> > Peo Sjoblom asks an interesting question. What international settings are
> > you using? Perhaps your delimiters are NOT the same as the ones we're sending
> > you.
> >
> > Try using different delimters.
> > Perhaps semicolons: =AVERAGE(LARGE(E6:W6,{1;2;3}))
> >
> > or maybe this will work:
> > =AVERAGE(LARGE(E6:W6,ROW(1:3)))
> > Note: commit that array formula by holding down [Ctrl]+[Shift] when you
> > press [Enter].
> >
> > Does that help?
> >
> > ***********
> > Regards,
> > Ron
> >
> >
> > "Belzbub" wrote:
> >
> > > Still the same, what can be the problem??

[Belzbub]

What the *#¤*¤!#"@£$ is wrong with this thing..... No matter what i do, copy
the formula and pasting it in the sheet it woun`t work. Can it be something
with the properties in excel? Can you DL the sheet, change it and mail it
back to me? hagudbra@bbnett.no I would be very happy if you were so kind.

Belzbub

Ron Coderre skrev:

> First, I had no problem putting the formula into your worksheet.
>
> Second, In your spreadsheet, you have less than 3 numbers in the range
> E6:W6 causing the formula to return #NUM!. When I entered a third number, the
> formula calculated properly.
>
> I changed the formula to this to trap the "less than 3 numbers" situation:
> =IF(COUNT(E6:W6)<3,"Less than 3 numbers",AVERAGE(LARGE(E6:W6,{1,2,3})))
>

[Ron Coderre]

Check your email.

Good luck.

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


"Belzbub" wrote:

> What the *#¤*¤!#"@£$ is wrong with this thing..... No matter what i do, copy
> the formula and pasting it in the sheet it woun`t work. Can it be something
> with the properties in excel? Can you DL the sheet, change it and mail it
> back to me? hagudbra@bbnett.no I would be very happy if you were so kind.
>
> Belzbub
>
> Ron Coderre skrev:
>
> > First, I had no problem putting the formula into your worksheet.
> >
> > Second, In your spreadsheet, you have less than 3 numbers in the range
> > E6:W6 causing the formula to return #NUM!. When I entered a third number, the
> > formula calculated properly.
> >
> > I changed the formula to this to trap the "less than 3 numbers" situation:
> > =IF(COUNT(E6:W6)<3,"Less than 3 numbers",AVERAGE(LARGE(E6:W6,{1,2,3})))
> >
>