Head to head

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Noob Jedi - 31 Aug 2007 15:22 GMT

I'm trying to statistically capture head-to-head occurences in a
sports league. Out of 120 teams, I want to capture only 35 of those
teams and how many times there is a head-to-head occurence within that
pool of 35. I know the schedule will show something like "Team1 vs
Team 2" and then "Team 2 vs Team 1." If they are referring to the same
game, I only want to count it as one occurence. Does anyone have a
method or spreadsheet that can help me do this?

Reply to this Message

bj - 31 Aug 2007 19:02 GMT

How is the data formatted?
how can you tell if it is the same game?
what do you want to do with the data. Are there multiple cells of data for
each game?
do you want summaries?
I could continue on with the questions, but hope you understand we need to
know what you have before we can make many suggestions on what to do. Most
of us are (or at least I am) egotistical but recognise I can't answer an open
question.

> I'm trying to statistically capture head-to-head occurences in a
> sports league. Out of 120 teams, I want to capture only 35 of those
[quoted text clipped - 3 lines]
> game, I only want to count it as one occurence. Does anyone have a
> method or spreadsheet that can help me do this?

Reply to this Message

Noob Jedi - 31 Aug 2007 20:36 GMT

> How is the data formatted?
> how can you tell if it is the same game?
[quoted text clipped - 15 lines]
>
> - Show quoted text -

Lol, thank you for honesty. Well let me give you whole scenario so you
can fully grasp what I'm trying to get.

I am statistically tracking the NCAA Football 07-08 season, since it
just started yesterday. Now, what I have so far are 40 lists from 40.
Each list consists of 35 teams ranked from 1st-35th place of how each
predict will be the outcome of the season. There are about 120
possible teams to choose from, but we are only concerned with the top
35 of each list.There is obviously a schedule of about 15-20 different
days that all these teams will play each other. Yesterday about 11
games occured, 11 occurrences of head-to-head games. However, take for
instance Buffalo and Rutgers. Their game ended with 38-3 wit Rutgers
on top.If on a given list, both appeared on that list, that is
considered one occurrence of a head to head game. But if only one of
these teams showed up on that list and the other does not, it does not
count as a head-to-head. I guess you could say, I want to create the
head-to-head statistic of each list.

So far, the way the data is formatted is just the list itself. From
columns C:AQ, it lists each individual list from C6:C40 and so on for
each column. I don't know how relevant this is, but the schedule shows
from columns B:S, it shows the date the games are to occur. Down
Column A, lists all the referencing teams. Accross the columns, it
shows what team they will play.

I hope that was thorough enough for you. But if not, let me know if
you need me to clarify anymore for you to suggest a setup. Thanks.

Reply to this Message

bj - 31 Aug 2007 23:26 GMT

the simplest way is to use a helper column with
=and(or(A2=(list}),countif({list},b2:AQ2)>0)
where list is a named list of your schools of interest
sum up the helper column and divide by 2 and you will have the number of
head to head games
you could then filter the helper column for 0s and delete and only the rows
with head to head games would be left.
select c2:AQ500
using conditional formating with a formula like =countif(list,c2)>0 and
format color to see what games are when
This still shows two entries for each game, but depending on what you want
to do, this might be an advantage.

> > How is the data formatted?
> > how can you tell if it is the same game?
[quoted text clipped - 43 lines]
> I hope that was thorough enough for you. But if not, let me know if
> you need me to clarify anymore for you to suggest a setup. Thanks.

Reply to this Message