Forum Discussion
Help please. Formula for Children's Athletics 100m Handicap.
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.
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.
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.
- Hi again Riny_van_Eekelen.
The formula is about 90% there thanks - only problem is that it doesn't calculate the last variation correctly. I have ran a couple of different scenarios and manually calculated the 'Unless Last Contestant Winner Again', and have had different results.
Before I ask if you could Kindly relook at the formula, may I ask you to explain your formula step by step (in layman terms please!) and I will see if I can find the fix.
Many thanks once again.
- 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.
