[Solved] Averaging the top 10 scores out of 20

[Solved] Averaging the top 10 scores out of 20

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

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, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.0; 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.

Zizi64
Volunteer

Posts: 8771
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Averaging the top 10 scores out of 20

Pivot tables are easy.
Attachments
pivot_t98676.ods
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.4

Villeroy
Volunteer

Posts: 27750
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Averaging the top 10 scores out of 20

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

MrProgrammer
Moderator

Posts: 3952
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Averaging the top 10 scores out of 20

it worked great, thank you
Open Office 4.1.3
Windows 7
mrmark

Posts: 2
Joined: Tue Jul 16, 2019 2:05 pm