Forum Discussion

Neilo420's avatar
Neilo420
Copper Contributor
Oct 20, 2020

Help please. Formula for Children's Athletics 100m Handicap.

Hi Excel Community.

I have been given the task of creating a spreadsheet to map the 100m handicaps for our local athletics club.

I have attached a pdf copy of the formula and what I have done so far.

Would there happen to be any Excel Guru's out there willing to lend me a hand?

Many thanks,

Neil.

15 Replies

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor

    Neilo420 Perhaps better to upload your Excel workbook (with some made-up names), since you already started one. And please indicate in a few examples what results you expect any formula to come up with. Difficult to visualise what you have/need on the basis of a PDF alone.

    • Neilo420's avatar
      Neilo420
      Copper Contributor

      Hi Riny_van_Eekelen. Sorry for the delay.

      I have attached our spreadsheet.

      The formula in question is on the Track Points sheet and starts on Column T, and follows suit AB, AJ, AR etc. This sheet will eventually be locked as it will be a data only sheet.

      It relates back to the Track Results sheet, which is the input data sheet.

      I have entered both attendance (ATT), and placings (in R3/100m column only as this is the handicap race) for the first few meets so you can see how it works to date.

      Any assistance or thoughts would be much appreciated.

      Many thanks.

       

      • Riny_van_Eekelen's avatar
        Riny_van_Eekelen
        Platinum Contributor

        Neilo420 Thanks for sharing the worksheet. When I opened it and started changing things a bit my machine became very slow. I noticed that you had 14 hidden sheets, full with formulae and many hidden rows in the two visible sheet, also filled with formulae and conditional formats. So I just extracted the 12 example rows (6 from Results and 6 from Points) and put them in one single sheet to see what you where trying to achieve with all the nested IFs and AND functions, in relation to your original PDF.

         

        And then I got lost and started from scratch, though trying to follow your logic. The result is attached. A small sheet with just a few races to test the different scenarios with.

         

        You'll notice that I used a beginning hcp and hcp+/- after every race and that I calculated them in the Results area. Now, I'm not sure why you also want to have it in the Points area, but I left the accumulated hcp in there (linked to the calculations in Results)

         

        The biggest change is that I got rid of all the nested IF statements and replaced them by CHOOSE. Easier to follow and maintain, should you ever want to change the rules. And to satisfy the consecutive winner criterion, I used a VLOOKUP in the hcp+/- columns for the 2nd race and the ones thereafter. Not very elegant but it works. If you are on an older version of Excel, you might have to re-enter these formulae and confirm the with Ctrl-Shift-Enter rather than with the regular Enter alone.

         

        If this is indeed in accordance with what you require, you can just copy columns AB:AH and paste it into AI to track the next race. If it is not, you need to explain more or perhaps just copy some of the mechanics of my sheet into your own.

    • Neilo420's avatar
      Neilo420
      Copper Contributor
      Thanks Riny_van_Eekelen. I'll look to attach a mock up spreadsheet as soon as I get the go ahead from our Club President.
  • Neilo420's avatar
    Neilo420
    Copper Contributor

    Hi community.

    Please disregard the last line on the on actual format (All Others +8m).

    That is not meant to be included.

     

    Many thanks,

    Neil.

Resources