Page 1 of 1

[Solved] Averaging the top 10 scores out of 20

PostPosted: Tue Jul 16, 2019 2:43 pm
by mrmark
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.

Re: Averaging the top 10 scores out of 20

PostPosted: Tue Jul 16, 2019 3:00 pm
by Zizi64
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.

Re: Averaging the top 10 scores out of 20

PostPosted: Tue Jul 16, 2019 5:35 pm
by Villeroy
Pivot tables are easy.

Re: Averaging the top 10 scores out of 20

PostPosted: Wed Jul 17, 2019 11:53 pm
by MrProgrammer
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.

Re: Averaging the top 10 scores out of 20

PostPosted: Thu Jul 18, 2019 2:27 pm
by mrmark
it worked great, thank you