Forum Discussion
Help please. Formula for Children's Athletics 100m Handicap.
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.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.
Riny_van_Eekelen Thanks for your explanation.
I have changed one thing on the first Choose option from 0,1,2 to -2,1,2.
One thing I do not understand is that according to the 'same winner again' scenario, the attached image should have shown the hcp+/- Z column as such;
- Row 15 - 3
- Row 16 - -2 (winner)
- Row 17 - 1 (second)
- Row 18 - 2 (third)
- Row 19 - 3
- Row 20 - 3
To my very simplistic eye, it appears that the formula only reads into the 'same winner' and negates the other placings, therefore using the standard formula of seconds back 1, thirds back 2, and remainder forward 2.
Have I missed something crucial here, or am I just oblivious?
Many thanks.
- Thank you very much Riny_van_Eekelen,
I'll have a good look at it and have a play and see if it does what's needed.
Really appreciate your assistance with this.
Kind regards,
Neil.
