TAB Mailbag: Determining GB in Excel

Yesterday, I received an email from LBS tourney organizer Darren Schulz asking me an Excel question.

Tom,
Hope this email finds you doing well. I have been running through various statistical blogs on your site in hopes to find a way to get a formula to work for Games Back in the standings. I have tried so many, and something is always off it seems.

I saw an image for your league that looked great, so I am wondering if you have any advice or possibly have something to share that I could manipulate for my own league.

Any ideas? I appreciate your time.
Thanks,
Darren Schulz

Darren must know I love answering Excel questions because I jumped on this immediately.

I remember when I set this up with my 1966 NL replay and it took some searching and testing before I got it right. By now, I think my Games Back (or Games Behind) formula works pretty well. I do check it manually every so often and so far, it hasn’t let me down.

Here is the formula.

Assuming first place wins are in cell C4 and first place losses are in D4, this is the formula I use in the first place GB:

=(($C$4-C4)+(D4-$D$4))/2

If you copy and paste, into the cells below, the relative cell references should change correctly.   That is, the cell references with the $ symbol will remain the same and the the others will reflect the change in location.

Just by copying and pasting the cell, the cell in Row 2 will look like this:

=(($C$4-C5)+(D5-$D$4))/2

…without having to type it out again!

You can format the cells in the Games Back column to display as a fraction as I have or in decimal format.

I hope that helps, Darren!