Results 1 to 3 of 3
  1. #1
    Lounger
    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

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 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

  3. #3
    Lounger
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •