Formula to calculate last 20 games

Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file.

Thank you for the heads up

Since you using Office 365, you can use Power Query which is called Get and Transform Data and found on the Data Tab. Look at the attached file to follow the step by step in the UI.

Please Login or Register  to view this content.

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.

Thank you alansidman, I appreciate your helpful links and explanation. I noticed the query table recorded the first 20 games each player played, however I would like the last 20 games instead.

Building on alan's proposal, I removed one row of the code so that it reads:

Please Login or Register  to view this content.

Next I added Column1 to the table on the Auto Season sheet populated using:

Formula:

Please Login or Register  to view this content.

Note that it may be possible to add that column in Power Query.
On the Auto-Standings sheet the Winner and Loser columns are populated using:

Formula:

Please Login or Register  to view this content.

The Win% column is populated using: =G3/SUM(G3:H3)
Let us know if you have any questions.

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.