Excel tips: adding Last 10 and Streaks to my Standings page

Warning: this article will contain some EXTREME Excel nerdiness. Read at your own risk:

Darren Schulz’s email about Games Back (which I posted about yesterday) came right as I was making improvements to my own Excel set up for my 1966 NL replay. For years, my standings page had the basics. I’m talking wins, losses, win percentage and yes, games back.

But I wanted more. I wanted to see which of my 1966 NL teams were hot… and not so hot.

In short, I wanted to add a “Last 10” column to display the record for each team in their past ten games. In addition, I wanted to add a “Streak” column to display how many consecutive current games each team has won (or lost).

Obstacles

I had a couple obstacles to overcome. One, my original set up has each game page for each team listed the scores by “Home Team” and “Visiting Team”. In short, Atlanta’s score was not in the same column because they may be at home or or they may be away.

In other words, in the first game played by Atlanta, I know that the Visiting Team scored three runs and the Home Team scored two runs. To me, it would be more helpful to know that ATL scored three and PIT scored two.

Rectifying this required me to add a few extraneous columns using the OFFSET formula which helped determine the actual runs scored for each team. Once I determined that, I knew if ATL won or lost for example.

Something like this…

=IF(D4="ATL",OFFSET(D4,0,1),OFFSET(D4,0,3))

So essentially, if D4 equals “ATL” return the value of the cell next to it (which is the score of Home Team. If not, return the score of Visiting Team.

Once have the score for the team in question, wins and losses are simple using a if statement with a “>” operand. This is what I used to calculate win (and loss) streaks.

Assuming N4 contains a 1 for a win and a 0 for a loss, place this formula at the top column (note: I put this at P4)…

=IF(N4>0,P3+1,0)

It is important that the cell above this formula (in this case, P3) must have the value of 0. As before, copy and paste to all cells below it and the relative cell references will resolve correctly.

Note, this formula is very handy for hit streaks!!!

That was a big hurdle. But I really wanted to calculate stats based on the current date or games completed. I needed a way to return the value of the last game played.

I used the LOOKUP command for help with this. I know that for each game, I enter in a Winning pitcher in the H column. If there is nothing in the H column, then I know that game has not been recorded.

So this is what I have done with success:

=LOOKUP(2,1/($H$4:$H$166<>0),ROW($H$4:$H$166))

I have this on each team’s page and all this simply does is return the row number of the last game played. At this point, it equals 77. If I add another pitcher in the Winning pitcher column (it actually gets linked from another spreadsheet but you get the idea), that number will be increased to 78.

With that formula stored in cell T8, I can do something like this:

=SUM(INDIRECT("n"&T8):(INDIRECT("n"&(T8-9))))

This is a simple Sum command for N77:N68 which equals the number of wins in the last ten games. The process is similar for losses. With that, I now have the number of wins and losses in Atlanta’s last ten games calculated automatically.

I have spent a little effort in formatting the data and linking the info so that you can see it on the standings page. You can see the efforts in the top image at the top the article.

I immediately noticed one thing as I did this. The Cardinals (83-79 actual record) who are woefully in last place, are actually playing .500 ball in their last ten games. There may be hope for them yet.

That’s a lot of geekiness for one article. I hope some of you will get some use out of it.

Happy rolling!