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

Copper Contributor

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.

15 Replies

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.

@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.

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.

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.

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.
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.

 

 

@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.

Screenshot 2020-11-02 at 05.55.05.png

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.

@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 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 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.