Formula to identify streaks from a Data Set of Results

**mrvp** · 01-31-2012, 05:50 PM

I have geared a spreadsheet to generate predictions for a Sports League based on Least Squares Regression of previous results and Poisson Random Variables (Team Scores that seem to fit as Random Variables).

I am trying to refine the data, to remove or identify results that aren't as likely as predicted. One factor I am looking at considering is streaks (e.g. Team A has won its last two games, Team B has lost its last two, hence I can decide that Team A is more likely to win a close game).

Columns I have in the table are:

A - Round (think of it as week 1, week 2 etc.)
B - Date (I'm not sure if this is formatted as a date)
C - Time of Game
D - Home Team
E - Home Goals
F - Away Team
G - Away Goals
H - Home Margin of Victory (E - G so it will be negative for a loss)
The other columns on the table hold other irrelevant data

I'm thinking I could probably do this with a COUNTIF formula, and may need to generate a winning team cell e.g. (=IF(H31>0,D31,F31) first, but I'm really not quite sure of where to go with this (or would I need to generate some kind of lookup table first - to almost count the current streak).

I would really appreciate any advice anyone may be able to offer.

Thanks,

**MarvinP** · 01-31-2012, 06:26 PM

Hi mrbp,

I've worked on tables like this and think your table above can be improved. Instead of Home Team and Away Team being different columns, have a single column for TEAM and another for Home/Away letter of A or H. Then Goals Scored and Goals Given away. You would then enter both teams that played and reverse the H and A letters and goal numbers. I'd also put in a column for Win/Lost.

For the consecutive streak column, I'd use a countif formula that counts the number of team names the same as the above with a Win. This might take two helper columns.

If you attach a file of what you already have it is much easier to explain and help.

**mrvp** · 01-31-2012, 06:49 PM

Hi Marvin,

I'm not sure that would work, as I have columns D and F on that sheet geared to a Solver model and a VLOOKUP table (I'm thinking though that I could just set this up as a separate worksheet for streak analysis as a way round this?).

I've attached the sheet that I'm interested in doing this on - as an FYI the Probabilities tab is rough working at the moment as is the Data Sheet tab.

I'm quite happy to bounce something back and forth (I hope this gives you an idea of where I am going with this).