Microsoft Excel Anyone?

So I have been going Excel crazy recently by trying to compile stats, keep score and just record wins and losses over several seasons for teams. So I’m going to focus this for anyone who is some what resourceful with Excel and wants to help me out.

My dilemma is that I have a column that is counting the streak of the said team. For example, let’s say if the team wins 5 in a row, loses 2 in a row and then wins one… the column will say the following:

W1, W2, W3, W4, W5, L1, L2, W1

Now that is a relatively small scope of what i have. I have recorded data of over 130 games so far, and I am trying to figure out a way to automatically calculate what is the longest winning streak, losing streak, etc.

I can provide the Excel sheet if someone wants to give it a try. Any help or suggestions of further stats to determine from the given data would also be greatly appreciated. Just leave your email in the comments or contact me directly and I can send you the file.

2 Replies to “Microsoft Excel Anyone?”

  1. Your best luck lies in VBA Scripts.

    But there may be a way to jerryrig the process with Excel forumlae – but it doesn’t seem like it’ll be very clean.

    Using macros and the VBA Editor, you can run loops that count the number of W# (or L#), store that max, and progress through the row until it reaches the first blank cell (i.e. the end).

    Once that’s functional, you can make that a nested loop within a loop that polls through all teams. Link that subroutine (i.e. function) to a clickable “Update” button and you can automatically revise the scores anytime you update the Excel sheet.

    I hope that helps. Let me know if you have any questions.

    – Rafi (from Rutgers YSP)

Leave a Reply