[Solved] Averaging the top 10 scores out of 20

Discuss the spreadsheet application

[Solved] Averaging the top 10 scores out of 20

Postby mrmark » Tue Jul 16, 2019 2:43 pm

Good Morning,
I work at a golf course and we have a weekly outing. Each week, I post the scores and their averages. Formula for that is not a problem. We are in week 7 and when we get to week 11, I need to change my formula to give me the top 10 scores only and to give me their average score. Thank you for any help.
Last edited by MrProgrammer on Sat Jul 20, 2019 4:08 pm, edited 1 time in total.
Reason: Tagged ✓ Solved
Open Office 4.1.3
Windows 7
mrmark
 
Posts: 2
Joined: Tue Jul 16, 2019 2:05 pm

Re: Averaging the top 10 scores out of 20

Postby Zizi64 » Tue Jul 16, 2019 3:00 pm

Use a helper table filled-in with 10 formulas by usage the function LARGE() Then calculate the average of the helper table.

Note:
For the state " less than 10 data exists", you need use a condition in the formulas to examine the COUNT() of the data.
Tibor Kovacs, Hungary; LO6.1.6 on Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.3.3; AOO4.1.6
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 8549
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Averaging the top 10 scores out of 20

Postby Villeroy » Tue Jul 16, 2019 5:35 pm

Pivot tables are easy.
Attachments
pivot_t98676.ods
(45.26 KiB) Downloaded 15 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27390
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Averaging the top 10 scores out of 20

Postby MrProgrammer » Wed Jul 17, 2019 11:53 pm

Hi, and welcome to the forum.

mrmark wrote:We are in week 7 and when we get to week 11, I need to change my formula to give me the top 10 scores only and to give me their average score
=AVERAGE(IF(COUNT(cells)<11;cells;LARGE(cells;{1;2;3;4;5;6;7;8;9;10})))
Type that then press ⇑⌘Enter on a Mac, Ctrl+Shift+Enter on other platforms. If you press Enter by accident, use Edit → Delete Contents → Delete All → OK, then retype the formula and try again. If you need any additional assistance attach a document demonstrating the situation (remove confidential information then use Post Reply, not Quick Reply, and don't attach a picture instead of the document itself).

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3900
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Averaging the top 10 scores out of 20

Postby mrmark » Thu Jul 18, 2019 2:27 pm

it worked great, thank you
Open Office 4.1.3
Windows 7
mrmark
 
Posts: 2
Joined: Tue Jul 16, 2019 2:05 pm


Return to Calc

Who is online

Users browsing this forum: No registered users and 10 guests