Results 1 to 3 of 3
Thread: Event results how!? (Excel 2003)

20080907, 06:09 #1
 Join Date
 Mar 2007
 Location
 Canada
 Posts
 32
 Thanks
 8
 Thanked 0 Times in 0 Posts
Event results how!? (Excel 2003)
I RREALLY need some help here!
I'm not much at Excel but have need of it on occasion. I'm ok with conditional formatting, can copy and modify formulas to an extent etc, but nothing too hugely complicated. Pivot tables  whuzzat?
The problem:
We currently run two events a year. Think orienteering event, where competitors get a choice of points to go to  no fixed route. Different checkpoints have different values. We start with a list of checkpoints and their value.
Competitors in rows, checkpoints in columns.
At the end of the day they hand in their routebooks  if they made it to a point, the admin person enters one character into the cell that corresponds to their name and the checkpoint. Nedless to say they don't have time to look up the score!
So the spreadsheet needs to calculate the persons score based on the checkpoints they got to that day. Same on the second day, then it's all totalled. There is a lot of interest in who had the highest score at the end of day one, so it's important to have that right. Then for day two, what did they get that day in comparison to day one, and of course the total.
Last year I did it so:
=IF(D25="y",6,0)+(IF(E25="y",23,0))+(IF(F25="y",27 ,0))+(IF(G25="y",30,0))+(IF(H25="y",76,0))+(IF(I25 ="y",81,0))+(IF(J25="y",114,0))+
(IF(K25="y",94,0))+(IF(L25="y",127,0))+(IF(M25="y" ,98,0))+(IF(N25="y",91,0))+(IF(O25="y",99,0))+(IF( P25="y",98,0))+(IF(Q25="y",95,0))+
(IF(R25="y",86,0))+(IF(S25="y",60,0))+(IF(T25="y", 107,0))+(IF(U25="y",72,0))+(IF(V25="y",122,0))+(IF (W25="y",103,0))+(IF(X25="y",103,0))+
(IF(Y25="y",125,0))+(IF(Z25="y",81,0))+(IF(AA25="y ",102,0))+(IF(AB25="y",55,0))+(IF(AC25="y",90,0))+ (IF(AD25="y",90,0))+(IF(AE25="y",40,0))+
(IF(AF25="y",110,0))+(IF(AG25="y",93,0))+(IF(AH25= "y",79,0))+(IF(AI25="y",83,0))+(IF(AJ25="y",100,0) )+(IF(AK25="y",96,0))+(IF(AL25="y",96,0)) etc...
for day one. But no way to get the proper results for each day, so basically it sucked  but we did have results for day one, and we did have a total that was right. BUT as soon as we entered the day two results, day one totals were wrong. So that method is BAD!
Perhaps if we used a different entry character for each day would help.
Remember we do two a year of these, and the scores and values are completely different, so a lookup table for the scores that's easy to update is ideal. The number of checkpoints varies too. Any suggestions for a nice lookup system? I'm sure there's some easy way to do this but I have no clue!
thanks a LOT!
Grant

20080907, 06:56 #2
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,270
 Thanks
 3
 Thanked 187 Times in 173 Posts
Re: Event results how!? (Excel 2003)
If you had your checkpoint values in row1, you could use a formula like:
=SUMPRODUCT((D25:AL25="y")*($D$1:$AL$1))
to give you the total for an individual.Regards,
Rory
Microsoft MVP  Excel

20080907, 07:49 #3
 Join Date
 Mar 2007
 Location
 Canada
 Posts
 32
 Thanks
 8
 Thanked 0 Times in 0 Posts
Re: Event results how!? (Excel 2003)
Cool! Yes that works a treat, thanks! Even easier than I thought. Not that I'd have ever figured it out, but now I see how it's very nice!
I knew this was the right place to ask...
thanks a bunch, Grant