Forum Discussion
Help please. Formula for Children's Athletics 100m Handicap.
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.
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.
- Riny_van_Eekelen - Thank you Sir!
Your adjustments have worked on my end too and all the calculations work.
You sir, are a scholar and a gentleman.
Many thanks once again for all of your help here.
Best regards,
Neil.Neilo420 You're welcome Sir!
Glad we could work it out.