Hope someone here can help me

[AlienBeans]

I'm not that knowledgeable on excel, and need a little help. I'm
confident Excel can do what i want, just don't know how to get it set
up.

I run a weekly college football pickem contest and want to set up excel
to do the scoring for me. We pick multiple games every saturday, and
some games are worth 1 point, and more important games are worth more.
What I want to do, is be able to set up these matchups and point value
for each, then when the games are over, simply type in the winner, and
have it match every player that has that team and add the respective #
of points. See, I have anywhere from 20 to 50 people a week
participating, and it takes forever now to score all players entries.
Can somebody here help me set this up? Thanks in advance.

 

[Dominic]

AlienBeans,

I would set up a lookup table.

Check Debra Dalgleish's site for a great explanation:
http://www.contextures.com/xlFunctions02.html

Basically, I would probably set up one set of column headings like: GameID,
Team1, Team2, Winner, PointValue. Enter the week's games here.

Then another set of column headings like: Player, GameID, Pick, Points

Then use a VLookup function to return the points for each pick by a player.

Then I would either use a PivotTable on the player pick table to summarize
the data, or you could use some SumIf functions. I would use a Pivottable
personally.

HTH

 

[AlienBeans]

Thanks. thiat is way over my head, but perhaps i can figure it out.
thanks again.

 

[Dominic]

Give it a go and write back with specific questions.

To get you started:

A1 - GameID
B1 - Team1
C1 - Team2
D1 - Winner
E1 - Points

A2 - 1
B2 - Texas
C2 - USC
D2 - Texas
E2 - 5

G1 - Player
H1 - GameID
I1 - Pick
J1 - Points

G2 - Tom
H2 - 1
I2 - Texas
J2 - "=vlookup(i2,d2:e100,2,false)"

Then run a pivot table on G1:J100. Put Player on the left side (row area)
and Points (Sum) in the Data area (middle).

HTH

 

[AlienBeans]

Thanks a bunch. i certainlly appreciate the help. is there really a
need for the GAMEID? dont mind it, just curious. the vlookup is
really way over my head....

 

[Dominic]

There really isn't a need for GameID, except to help you keep track of which
games are which. The formulas will work without them.

the vlookup basically works like:

"=vlookup(i2,d2:e100,2,false)"

look for value in cell I2 (Texas) in the cells (D2:E100) where you have your
game information. When you find Texas, give me the value in row 2. The False
means find an exact match, if there is no exact match, then you will get an
error.

Try this out with some sample data and let me know how it goes.

Your welcome for the help.

 

[AlienBeans]

dang, that really works. Here's another question. I'm concerned about
the format a little. The reason is, that we usually have anywhere from
20-40 players participating, and we are usually picking anywhere from
12-20 games a week. When the player name going down, the sheet could
go on forever. Could this same thing be formated with each PLAYERNAME
taking a column, with all that players picks listed underneath? Then
have the point total show up at the bottom of the spreadsheet?

 

[AlienBeans]

Also, I'm having a problem understanding the pivot table. I just can't
seem to get this to work properly.

 

[Dominic]

Sorry for the delay. I've been swamped.

Hope to get on a little later this afternoon.

What does the pivot table look like?

 

[AlienBeans]

I'm too clueless to figure it out. Do you mind emailing me?

Shoot me one at auburn2005 at gmail.co