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.

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 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 I'll try to explain what I had in mind. And since you did not mention you received an error message (just an unexpected result) I assume your Excel version can handle the VLOOKUP part of the formula. The formula in the "hcp+/-" column S is like this:IF(IFERROR(VLOOKUP(1,J$15:J$20*Q$15:Q$20,1,0),0)=1,<same winner as previous race>, <different winner>)*<attended>The part J$15:J$20*Q$15:Q$20 creates an array of numbers where each cell in J is multiplied by its corresponding cell in Q. Only if the winner in the current race (Q) is the same as the previous (J) this array will contain a number 1. VLOOKUP thus looks if there is a number 1 in that array. If it exists it will return 1, if not #NA!. To get rid of the #NA! error, I wrapped the formula in an IFERROR. So, now the outcome is either 1 or zero.The IF function then tests this outcome. IF 1 then calculate handicap based on "rules for same winner" else calculate handicap based on "rules for a different winner." Each of the handicap calculations use CHOOSE. It takes the result (1,2 or 3) and chooses the relevant handicap adjustment (0,1,2 for same winner) or (-2,-1,0 for different winner). Is a runner has no top three result an error will occur. Similarly to what I described before, the IFERROR function then captures the error and the handicap adjustment becomes 3 or 2 respectively.Finally, I multiply the whole lot by the attendance number (1 or 0) to make sure that the handicap is adjusted only if the runner took part in the current race.Hopefully, this helps you understand the formula and resolve your problem. Perhaps, by adjusting the values to choose from in case the winner is the same.

Neilo420 I applied the change you indicated in the "same winner" calculation, and arrive to the desired handicap adjustment. Please see the attached file and the picture below.By the way, the formulae in columns R, Y and AF now also include a test that sees to it that the handicap can not go below 0, as I believe that was also one of your requirements from reading your original formulae. I had already included a test to maximise the handicap to 30.

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.

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

15 Replies

Riny_van_Eekelen
Platinum Contributor
Oct 20, 2020
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
  Copper Contributor
  Oct 29, 2020
  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.
  
  G.A.C. Spreadsheet - Handicap.xlsx3.8 MB
  - Riny_van_Eekelen
    Platinum Contributor
    Oct 30, 2020
    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.
    AtleticsClub.xlsx18 KB
- Neilo420
  Copper Contributor
  Oct 21, 2020
  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
Copper Contributor
Oct 20, 2020
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.

Forum Discussion

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

15 Replies

Resources