pivot table setup

**andyman3** · 10-06-2011, 10:05 PM

Hey

I am trying to make a spreadsheet for the purpose of tracking how accurate a guess is for an nfl pick.

So what I have is a schedule, and will after each game mark a winner. If the guess was correct, I want to be able to assign each team playing 1 point if correct. If the guess was incorrect, I want it to subtract a point.

I want to be able to update this every week and have the total points tally up weekly, so I can see what teams are getting more correct guesses and what teams are getting wrong picks.

I hope this makes sense. I was thinking a pivot table would work, however it was only giving the points to one team, and I couldnt figure out how to do it for both.

Can anyone make a very simple version of this, you can just use team a, team b, etc, and I dont need scores.

When I tried, my categories were
away team, home team, winner, guessed winner, points

the home and away fields I have, the winner I have data up through the last game, guessed winner I have, and points was just a =if(guessed winner = winner,1,-1)

Thanks for any help!!

**andyman3** · 10-07-2011, 03:39 PM

I just uploaded where I am stuck at. I think the reason I am not getting finding the result I want is guessed team is the only one it will assign the points to. I need to find a way for it to assign points to both teams, but still be able to keep track of the points in one drop down

**ChemistB** · 10-07-2011, 04:16 PM

In G2
=IF(ISTEXT(E2),IF(F2=E2,1,-1),"")
In H2
=IF(ISTEXT(E2),IF(E2=F2,B2,D2),"")
In I2
=G2
In K2 (Header in K1 = Winners & Losers)
=IF(MOD(ROW(A1),2)=1,INDEX(E:E,INT(ROW(A3)/2+0.5)),INDEX(H:H,INT(ROW(A3)/2)+0.5))
In L2 (Header in L1 = Total Pts)
=IF(MOD(ROW(A1),2)=1,INDEX(G:G,INT(ROW(A3)/2+0.5)),INDEX(I:I,INT(ROW(A3)/2)+0.5))
Drag/copy everything down to bottom of schedule.
Create pivot table from K and L
Does that work for you?

**andyman3** · 10-07-2011, 04:33 PM

that idea would work great, but it looks like theres a problem with the formula for H. It seems that if the winner is not the home team, it claims the winner is also the loser