Forum Discussion
Help please. Formula for Children's Athletics 100m Handicap.
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 It works!!!
Thank you so much. The problem was the Array Formula - I use a Mac, so I transferred the working spreadsheet to a PC and used the 'shift+ctrl+enter' trick, and it worked!!
Thank you and I really appreciate what you have done to help me by figuring this out.
You are a true Guru!!P.S. I tried to find what the equivalent of 'shift+ctrl+enter' would be for Mac, and it didn't work for what ever reason. I came across someone advising you need to use 'shift+command+return', but that didn't seem to work... oh well.
Neilo420 Please see attached. Note that I also change the "Points" formula to a CHOOSE function. Much easier. It was already in my original file, but perhaps you overlooked it. I also lined-up the Points and Results schedules (i.e. made sure that the blocks of data for each event are starting in the same column. That makes it easier to write/maintain formulae and copy them across.
Riny_van_Eekelen Hello again.
So - very excited that the formula is working well on your spreadsheet, but when transposing across to my spreadsheet, I have had to tweak it again ever so slightly.
Because I use two seperate pages for my points and results, I have had to link the data from the points page, back to the results page. By doing this, I have lost the 'winner wins again' part (or more likely, I have not linked the correct part to the equation). May I trouble you to please relook at my mess?
I have attached a 2 page workbook for you to have a look at.
The part in question is under Track Points AE, whereas AE-8 should read +1, AE-7 should read +2, and the others (apart from AE-4) should read +3
Thanks again Riny.
P.S. - why have I linked back to Track Results?
Because on Track Points the data isn't 1, 2, 3 as in the results, it is the actual points for placing, i.e., 1st = 5 points, 2nd = 3 points, & 3rd = 1 point.
Neilo420 You're welcome Sir!
Glad we could work it out.
- 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.