Creating a formula to return the average of the top 5 scores for a certain player in table

Edited: Needed to fine tune the last formula.

Hello Drewtime. Welcome to the forum.

Try in E2 this formula to get the unique players names.

Formula:

Please Login or Register  to view this content.

Then in D2 this and copied down.

Formula:

Please Login or Register  to view this content.

Yields this:

	D	E
2	5.116	James B
3	6.3	Chris W
4	6.782	Al H
5	6.022	Justin W
6	6.184	Joe R
7	5.954	Daniel F
8	6.622	Josh D
9	4.378	Kyle N
10		Frank A
11	6.194	Wags
12	5.934	Andrew S

If you've a mind to use the newer Office 365 functions.

Formula:

Please Login or Register  to view this content.

An alternative with Power Query and a bit convoluted

Please Login or Register  to view this content.

Excel 2016 (Windows) 64 bit

	E	F
86	Display Name	Average
87	Al H	6.782
88	Andrew S	5.934
89	Bill B	5.57
90	Chris W	6.3
91	Daniel F	5.954
92	Frank A	5.225
93	James B	5.116
94	Joe R	6.184
95	Josh D	6.622
96	Justin W	6.022
97	Kyle N	4.378
98	Wags	6.194

Sheet: ScoreMagic

I took a pivot table approach.

I converted the data into a table. Since tables know how big they are you can continue to add data and you do not have to make any adjustments other than to refresh the pivot table.

I added some helper columns to the data. Column D computes the rank of the score and column E determines if the score is within the top 5 for the person.

The pivot table in columns A & B is the "answer." The pivot table in columns E&F are there for QA to confirm that the average of the top 5 is what the first pivot table shows. You don't need this table

FYI:

With Power Query, if you make changes to the source table and then select Refresh All on the Data Tab, your output is automatically updated. No need to make any changes.

Thanks for the feedback and marking the thread solved.

No thank you guys. Any of these solutions will save me a TON of headaches and manual efforts. I was able to get the answers but it would have taken manual updating of lists per player using AVERAGE & LARGE nested formula every week and that plain stinks.

If I remember correctly power query is just an add-on you have to enable right?

My other question was let's say I want to share this file with someone else to update in my absence. Will they need to know how to use power query and have the add-in enabled in order to have the data update? Trying to make this as easy as possible to manage should I be out one week and need someone to step in and manage the updates.

Not true. In your version it is built in and called Get and Transpose Data. So long as they are running a version with Power Query, all they need to do is update the source file which also contains the query and click on refresh

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

To list those qualifying players (using the source data from the first upload)

Formula:

Please Login or Register  to view this content.

	E	F
86	Name	Top 5 Avg PPR
87	Justin W	6.022
88	Joe R	6.184
89	Josh D	6.622
90	Andrew S	5.934
91	Daniel F	5.954
92	Chris W	6.3
93	James B	5.116
94	Al H	6.782
95	Kyle N	4.378
96	Wags	6.194

Does this formula replace the one in your original post or should i make a separate table to show the qualifiers after we did the pivot table to show the average of the best 5 weeks?

Or do the whole lot in one calculation. No copy/pasting. Paste this into a single cell (make sure the surrounding area is empty)

Formula:

Please Login or Register  to view this content.

Hi Glenn,

THanks so much for this. I was super excited to try this out but ran into problems. I downloaded this file and was adding some additional scores just to play around to see if the formula you created will work. As soon as i changed the data i got the "#NAME?" error and the message said unsupported function related to "xlfn". I copied the formula out of the file when i open it and its as follows.

=LET(A,SORT(UNIQUE(Table1[Player Name]),1),B,_xlfn.BYROW(A,_xlfn.LAMBDA(_xlpm.x,IFERROR(AVERAGE(_xlfn.EXPAND(_xlfn.TAKE(SORT(FILTER(Table1[PPR Score],Table1[Player Name]=_xlpm.x),,-1),5),5,,NA())),"Insufficient data"))),_xlfn.VSTACK(_xlfn.HSTACK("Player Name","Average PPR Score"),_xlfn.HSTACK(A,B)))

I noticed both excel put in "_xlpm.x" and "_xlfn." in multiple spots so i tried to remove them and the formula still didn't work. The file opens with the correct results, but it seems whenever i try to change any of the data the formula breaks and i cannot get it to work except to close without saving changes and reopen the file you attached.

I then tried to just copy and paste the formula you supplied above but that returned an error saying something needs to be corrected in the formula syntax.

Any ideas?

What Excel VERSION are you using? I t will work on O365, with any version >= 2202, which was introduced in March 2022. Is your Excekl up-to-date? Check your version and, check for updates and report back EXACTLY what your excel product is...