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
