 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / November 2005Average of top 3 numbers.
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?? does this help =SUM(LARGE(E1:E14,{1,2,3}))/3  SignatureDon Guillett SalesAid Software donaldb@281.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?? I just get error when i try it... perhaps you didn't change from my EXAMPLE. Try this. =AVERAGE(LARGE($E$6:$W$6,{1,2,3})) SignatureDon Guillett SalesAid Software donaldb@281.com >I just get error when i try it... Hi Maybe =AVERAGE(LARGE(E1:E14,{1;2;3})) Arvi Laanemets > does this help > =SUM(LARGE(E1:E14,{1,2,3}))/3 [quoted text clipped - 7 lines] > > top 3 results from collum E6 to W6 and top 3 reults from Y6 to AJ6. Can > > anyone help me with this one?? or even =AVERAGE(LARGE($E$1:$E$14,{1,2,3})) SignatureDon Guillett SalesAid Software donaldb@281.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?? =AVERAGE(LARGE(E6:W6,{1,2,3})) Same for other range. Gord Dibben Excel MVP >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?? Still error, hm..... What kind of error? SignatureRegards, Peo Sjoblom (No private emails please) > Still error, hm..... 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..... 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... > [quoted text clipped - 12 lines] > > > Still error, hm..... 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? [quoted text clipped - 17 lines] > > > > > Still error, hm..... 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... > [quoted text clipped - 10 lines] > Regards, > Ron 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 > Nope, when the message pop up and i press enter, "W6," in the formula is > marked... [quoted text clipped - 15 lines] > > Regards, > > Ron Still the same, what can be the problem?? 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 > Still the same, what can be the problem?? 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 [quoted text clipped - 15 lines] > > > Still the same, what can be the problem?? 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 > Still the same. I have put the Excel file on my homepage: > http://home.no.net/belzbub/Bedriftsskyting/index.htm and go to [quoted text clipped - 23 lines] > > > > > Still the same, what can be the problem?? 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. > [quoted text clipped - 4 lines] > 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}))) Check your email. Good luck. *********** Regards, Ron > 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 [quoted text clipped - 13 lines] > > 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}))) Is everthing a number. test with =isnumber(a1) > Still error, hm..... Everything is numbers yes. David McRitchie skrev: > Is everthing a number. test with =isnumber(a1) > > > Still error, hm..... Sorry was a bit early. When i mark the whole area i get FALSE. > Everything is numbers yes. 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 > Sorry was a bit early. When i mark the whole area i get FALSE. > > > Everything is numbers yes. 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 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})) SignatureRegards, Peo Sjoblom > Sorry was a bit early. When i mark the whole area i get FALSE. > > > Everything is numbers yes. 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!) SignatureArvi Laanemets ( My real mail address: arvil<at>tarkon.ee ) > Still error, hm..... |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.