Tracking Players | commanderruminations

Commander League: Tracking Players

So you've got your scoresheets and have held your first week of EDH League. What now?

Now you have the unenviable task of making sense out of all the chicken-scratches your players have handed you. I highly advise you to check over each sheet for basic stuff before you file it away. A player that fails to put down Gen points will usually be glad to do so if reminded that it is important to you that they do so. Names also get left off and totals are miscalculated. Do your best to make sure their sheet is as accurate as possible, and don't be afraid to make corrections after they have handed in their sheet even if you can't run it by them first. Math matters, and you owe it to your players to help get the points right each week.

As a side note - nobody has any obligation to tell their opponents how they awarded Gen points. Hopefully you won't ever see a dispute over them, but if you do, please try to intervene and make sure they understand that every player can award them however they see fit.

While it might seem like a good idea to just keep track of everything on paper, I would urge you to use a spreadsheet. It will allow you to make the numbers available to your players online and will help avoid the inevitable calculations errors we humans are prone to occasionally make.

I use Google Sheets to track the NexGen EDH League. It worked well for me and I've learned a few tricks I can share here on how you can do the same.

Monthly Results

You will want to set up a spreadsheet for each year you will be tracking. I don't see much sense in having multiple years on a single spreadsheet, as you'll already be using a tab for each month and tabs for your year end totals. You will definitely want to set it up so that only you can edit it, but others can view it. If you will be sharing it with the league, definitely put notes and details in where appropriate, as you have an audience beyond just yourself that will be using it as a resource.

A full understanding of spreadsheets is beyond the scope of this website but I'll do my best to get you started.

To the right is a shot of the top few rows of our February EDH League results. I separate out Gen Points and My Points and run totals on both, along with a combined total

Each cell that shows a total value will have a function entered in the cell definition area. Chip's week one combined total of 10 Gen points (from cell B3) + 10 My points (from cell C3) would be represented by a value of =SUM(B3:C3) in cell D3. His GRAND TOT month winning value of 77 points would be represented by a value of =SUM(D3,G3,J3,M3) in Cell P3. In the first example, B3:C3 means all values from B3 to C3 (a "range" of cells), and in the second example D3,G3,J3,M3 means the values in those four cells, but not any other values (a "list" of cells).

Setting up each month is a bit of work but isn't too confusing. Use the SUM functions to set up your totals and use shading to keep things organized visually. You can click and drag to select a range of cells and then use the Data menu to choose Sort Data by Range to set up the sheet so the top point getter is at the top. If you're unsure of what you are doing, you can always duplicate a sheet before you start messing around so that if you screw up, your data for that month is still safe somewhere.

You can set up one sheet and then duplicated it and the work you did on the SUM cells won't need to be re-done. Some months may have five weeks of play, based upon what day of the week you are playing. Usually it will be 4, but some months you'll have 5 and some months you may have to skip a week due to a holiday, PPTQ or other scheduling issue and you'll only have 3 weeks of play.

Named Ranges

When you are looking at multiple months of data, you may start wondering what your yearly totals look like.

This is where things get funky.

You're going to need to set up a thing called "named ranges" to represent the data on each of your month sheets. In Google Sheets you will use your Data menu and select Named Ranges. Then you will use the name of the sheet followed by an exclamation point and then the range of data on that sheet. In my spreadsheet I named each tab with the name of the month. For April, my data was in rows 3-38 and in columns A-S, so my named range was April!A3:S38.

When I create a new month tab, I copy the previous month and then clear out the numbers, so each month my results have more names on it than the last month. I do this to decrease the chance that I will enter a name differently in two different months, thereby messing up the ability to total up numbers on the end-of-year sheets for that person.

Yearly Results

Now that you have your named ranges set up, you can set up your end of year tab. I set up tabs for the NexGen league for Total Gen Points, Total My Points, Total Combined Points, Average Gen Points, Average My Points, Average Combined Points and a "Gap" page that evaluates the difference for players between their Gen Points and My Points totals for the year.

These tabs will look different. To the right are the top few rows of our 2016 Gen Totals. We started in February, so there is no January column.

You will copy the column of names from your December sheet, or from the sheet that has the names of everyone who has played with you that year, and paste it into the names column for your totals sheet.

Then in each cell in that top row you will need to enter a formula that will serve to go and look up a value in another sheet and display it on this sheet using the named range you created for that other sheet.

In the example above for my Gen Points February total, the following formula is in place for cell B3.

=IF(ISNA(VLOOKUP(A3, FebStats, 14, FALSE)),0,VLOOKUP(A3, FebStats, 14, FALSE))

This formula is using a number of functions.

VLOOKUP(search_key, range, index, [is_sorted])

This formula will look for a value within a range and return the value at the index number, so in the above example the formula is looking in the named range "FebStats" for the text in cell A3 and returning the value in the 14th column.

ISNA(value)

This fomula will return TRUE if the value is not available. This means that you are doing a lookup like the one explained above and the value in A3 is not found in the named range, the result is TRUE. Otherwise the result is FALSE.

IF(logical_expression, value_if_true, value_if_false)

This formula will evaluate the logical_expression and return the first value if true and the second value if false.

So in short, if the name in A3 of the current sheet is not found in the named range "FebStats", the value for the cell is 0. If the name in A3 is found in FebStats, that value will be displayed. This is important because you will have month sheets that are missing names from your final list of every player who has played in the league for the year.

You can now copy that set of number values (created with the function above) and paste it into the area for all the other month values for names below the top name.

Once you have all of the month values appearing correctly you can put a SUM function in the final column to total up each value in the row. Copy that down the sheet for each player so every row has a total shown. Then you can select the data and use that Sort Data by Range option in the Data menu to show the players in order by their total.

If this all seems confusing - it is, but if I can learn it, so can you. Click here for more resources on using google spreadsheets. Searching function names, including those listed above, should help you get going.

Yearly Averages

The totals for points are impressive and worth looking at and celebrating, but they don't tell the whole story of your league and the many players who have joined you in your games.

Our inaugural 11 month season wound up having 43 weeks of play. I was able to make 42 of those - only missing one week when I lucked into a pair of Boston Bruins tickets and went to the hockey game with my son. They won 1-0 in overtime. There were fights. It was awesome, but I digress...

By playing in 42 weeks of EDH league I had more opportunities to earn points than any other player. My son missed one additional week due to illness, so he played 41 weeks and after that the three most active players joined us 34, 32 and 32 weeks. It should come as no surprise that my son and I had the highest total points in all three categories (Gen, My & Combined Points).

Does that mean we're the best players?

Not necessarily - that's where we have to look at average points earned per week.

This spreadsheet is going to look a little different because we have to look at every single week of the year. I had to zoom out to get the screenshot below of our league's 2016 Average Gen Points.

You'll get your list of names from the last month tab or whatever tab has the full list of everyone who has played in your league this year. If you're copying each month December should work just fine.

The cells that display data from each month are basically the same as shown earlier. The function below is what I used for cell B3 in the screenshot.

=IF(ISNA(VLOOKUP(A3, FebStats, 2, FALSE)),0,VLOOKUP(A3, FebStats, 2, FALSE))

The key is to make sure every single entry has the correct Named Range and is looking at the right index from that named range. This process is something that will benefit from spot-checking your results to make sure you didn't mess up. You probably will. It's complicated stuff and it's easy to get things wrong.

Once you have set up each cell in the row for the entire year, and once you've doublechecked and caught any issues or errors you made, you can copy the cells in that first row down the spreadsheet for every other player.

The TOTAL column in my screenshot is misnamed - this is an sheet for your Average points. To get this average I used the following formula:

=ArrayFormula( AVERAGE( IF( B3:AR3 <> 0 , B3:AR3 ) ) )

This is using a number of functions to get our average while dropping out any zero values so weeks a player didn't attend aren't included.

IF(logical_expression, value_if_true, value_if_false)

This formula will evaluate the logical_expression and return the first value if true and the second value if false.

AVERAGE(value1, [value2, ...])

This formula simply returns the average of all numeric values in the range provided.

ARRAYFORMULA(array_formula)

"This enables the display of values returned from an array formula into multiple rows and/or columns and the use of non-array functions with arrays." Yeah. I copied this formula from some website somewhere, so... I'm not 100% sure but it works and I'm OK with that.

So the formula averages out the non-zero values, which is what we want.

The final column displays the number of non-zero values for that row, or in other words, the number of times that player joined us for EDH league. That formula looks like this.

=COUNTIF(B3:AR3,">0")

It's fairly straightforward, but I'll provide the formula definition anyways, just to be thorough.

COUNTIF(range, criterion)

The range in the example is B3:AR3 and the criteria is that it must be greater than zero to be counted.

Pretty simple, but now there's another issue we need to look at.

A baseball player who only plays 3 games won't be eligible for the batting title. In the same way I had to decide how many weeks of league a player would have to attend to be eligible for an end-of-year awards.

I wound up picking 50%, so out of 43 weeks a player would have to have attended at least 22 weeks of play.

I decided that I wanted to get fancy and have each row show up shaded if the player had met the criteria for being eligible for the end of year "highest average" award for that category. You can see in the screencap above that I was able to make a row appear in light green. How did I do that?

For this we used Conditional Formatting. It's under the Format menu option.

You can see the interface to the right - you simply choose a range of cells that will be affected, set up the custom formula and the formatting you would like applied.

For my spreadsheet I needed the formatting of a green background to only appear if the count was greater than 22, so I used =$AT3>22.

This value is in place on row 3, but when you copy it down for every other player, it should adjust appropriately so that it works.

One last note about averages: I didn't track rounds separately from weeks, so if a player attended but only played one round they would see that one round's point total displayed as that week's total. If you want to go to that length for your league, feel free, but I'm not really sure it's worth it. Tracking rounds separately would allow you to track commanders for later analysis, but I'm still not convinced it's worth the effort.

Gap Analysis

We've got sheets set up now to show the totals and the averages for our year's worth of EDH League data. There is one more way to slice and dice our data that I personally find really interesting.

All of our players earn Gen Points and all of our player earn My Points. The Gen points are awarded by other players. The My Points are earned directly your own actions in the game. I decided to set up a sheet that would report on the gap for each player between the number of My Points they have earned and the number of Gen Points they have been awarded.

This sheet is set up like the Total sheets, but in each cell where we had been putting the total for that month for that category, we instead use a new formula:

=IF(ISNA(VLOOKUP(A3, FebStats, 16, FALSE)),0,VLOOKUP(A3, FebStats, 14, FALSE)-VLOOKUP(A3, FebStats, 15, FALSE))

Dumbed down, this formula is looking at FebStats and if the player's name is found in the named range it returns their Gen Point number minus their My Point value. A player with 20 gen points and 5 my points would show 15 as the value displayed.

Set up this formula for each cell in the first row, make sure it's correct, set up a total cell at the end of the row and then copy them down the sheet for all the other players in the league.

Now you can sort players' gap values and see who appears at each end of the spectrum.

The big question of course is what this all means.

"Gen" points accrual is roughly tied to sportsmanship but more than anything else I think they represent the players who other players generally like to have at their table.

"My" points are a barometer of how competitive a player is. I think of that as "spikiness" but am aware that might be an inexact description. If nothing else, players that accrue lots of "My" points are winning - usually a lot.

When you look at the player that has the largest "gap value" in favor of Gen points, they will probably be a casual player that everyone likes to play with. They may not win a lot but when they do, everyone is happy for them. When they lose, they probably don't get salty or show a bad temper. Also - there's a decent chance they are a group hug player.

When you look at the player that has the largest "gap value" in favor of My points, they will probably be a very competitive player who plays in a style that other players don't always appreciate. Combo players and players who use very highly tuned decks are likely to rack up the My point totals like crazy but get a little less love from their fellow players in the form of Gen points.

I think it is important to honor both of these extremes in end of year awards, as they are both notable and both help to make the league a fun, challenging, interesting place to play EDH.