Help to automate ranking of tied teams in sports scoring program

I am seeking to improve my current Excel sports team scoring program by refining my existing scoring and ranking program to see if I can automatically rank tied teams rather than manually and I attach a sample of my worksheet and would be very grateful if anyone can help.

A Win “W” Loss “L” result is entered into the top Results Grid and this data is used to show Played, Wins & Losses in the adjoining columns. A measured average distance figure (LSD) in which the highest figure is dropped (the lower the better) is also entered and can be used as a final criteria to determine ranking after the final game where teams are still “tied.”

I wish to automate the process to avoid entering the final ranking manually in column R after the final game and wonder if it is possible to create a formula that would do this for me. Also I would like to print page 2 after each game with ongoing and final ranking information by creating a macro(s) but is it possible to use excel instead that would automatically carry over the data to the form from game 1 to 7 every time a score is entered and fill in the Final Ranking col only after the last game?

Teams play 8 games and from game 1 to 7 the ranking from top to bottom is based on the following criteria. Teams are ranked according to their win/loss record, if tied the team who have played the lesser number of games ranks ahead, if wins/losses are equal then the lower LSD figure ranks ahead.

After the final game, rankings are then shown in col R as 1st, 2nd, etc. and are based on W/L, record of results between teams and finally if still tied the LSD and these results would then appear on Page 2.

I hope I have explained my problem fully and that someone can help or even give me some suggestions.

Thanking you in anticipation.

Wow, I will have to study this one it looks so complicated, lets see if I can figure it out? Will be in touch once I have figured it all out. Many thanks again Alan

I think I can follow your ideas and very clever indeed and I don't know how you managed to do it so quickly, however it doesn't seem to take into account the LSD, if you enter say185 insted of 23.9 into K26 this would increase the LSD total and therefore should make it 2nd rank instead of 1st but it doesn't so there is something not quite right.

Also I haven't tried it all yet but wondering if there is any way that it would only put the Final Rank number position when it's the last game and leave it empty until then as that the only time it counts and how difficult is it to make it 1st, 2nd, 3rd etc. Again many thanks and apologies for making it so difficult. Regards

Hi there, what a brilliant website you have great colour scheme, love it puts mine to shame!

I am not with you as in your last attached file I make the results between FIN, ITA & SVK on 2W 1L each and GER on 0W 3L (you show GER on 1W 2L) who are all on 5W & 3L as you say so if you agree with me then GER is 4th (which is what your rank shows) so we would then have to look at the scores between FIN, ITA & SVK and I make it as they have 1W 1L each you need to go to the LSD to sort them out as they are all equal and the LSD (the lower number the better) would then make it ITA 1st, SVK 2nd & FIN 3rd if you see what I mean.

So I think the LSD will always be necessary at some time or another and sure as anything it will crop up in some other competion as soon as you have 3 or more teams tied on equal W & L's. Hope you can sort it out as other officials say it can't be done automatically and I would like to prove them wrong. Thanks again for all your efforts, Alan

One thing if you did produce a miracle I am not sure but when I said only to put the final ranking on Page 2 in cols QRS I meant that I would have liked that only to appear after the last game but I would have liked all the other info in the other cols to appear after each game/draw so as I can print and distribute this form as the games go along if you follow. In other words the FINAL RANK col would only be filled in after all the games were played but would be blank up until then but teams could see the results after each round. Alan

Please don't be sorry it's me that's giving you all this work and I have been playing around with different scores and you seem to have pulled off the impossible, brilliant, didn't think it was possible!

It's great that the final rankings now don't appear until the last game which is perfect but the program used to rank teams after every game on Page 2 which is the important part as that page is printed and handed out for informationbut but that doesn't happen anymore so is it possible to "rank" the teams on Page 2 on W & L etc as we go along after evry game but leave the final rank on cols QRS 39:47 blank until the last game as at the moment

But please if you are not sure what I mean please ask me as I don't want to waste anymore of your time also;

why does a blue background appear sometime?
What would happen if a tied team is also tied with the same LSD figure? and
Under my old system I could put in an X against both teams to show that (very occasionally) a game wasn't played is there any way round that?

Many thanks again Alan

I wish I could put in 10 stars you deserve it for what I thought was impossible.

Yes just about there now and if I understand you correctly I would much prefer B9-26 to stay at 1 to 9 until the last game as before.
Col R9-26 looks good but it is not necessary as the ranking gets all sorted out anyway so I don't need to see who the tied teams are.
At the start when the grid is empty with no data the ## sign comes up so I guess I can put in if """,""", before your formulas to show if empty keep cell blank as it looks better.
Sometimes where 2 or more teams cannot qualify for the semi finals they will decide not play and I would like to be able put in an x against both teams instead of a W or L but this complicates your formulas especially as there will not be 72 games, can you change this?

If you are not sure what I mean please ask me! Alan

Thanks again for the changes but no it can happen that 2 teams or more might not have won a game and decide that as they can't qualify for the semies they might not bother playing, it's unusual but can happen, so they could pull out of the competition at a late stage so what I have done in other programs is that if an "X" appears against both teams instead of a W or L then it's game not played but they still appear in the ranking list but with 1 or more games less if you get my meaning in other words the program recognises either a W,L or X but the X just means a game less but the team is still listed and ranked until the end.

Hope that is clear. Alan

Thanks again that seems to have solved it, excellent.

But I attach your last file and and I have put 5 teams on 4W as a test and if you look I think there is an error in the ranking as I think GER and SUI are the wrong way round as GER beat SUI and the LSD doesn't come in to it and I think should rank ahead of them, have a look and see what you think. Alan

Sorry to be slow in coming back to you but the forum didn't email me to say you had replied as it normally does so I thought you were still working on it.

Not quite sure what you mean but I think you are saying that your program can be adapted to either rank tied teams where a "head to head W or L" result will decide or if it is an LSD (a low number must always be best) that decides but it cannot be mad to do both.

Putting in the score is not something I would like to do as actual scores don't really matter only W & L as a game is always played on until we get a result and an LSD (last stone draw) is only taken at the beginning of each game in case at the end of the competition team are tied on W & L and can be used but only as a final decider if there is no other way to "break" more than 3 tied teams.

So I can't think of where we go from here so does anything I have said make any difference?

I now see what you mean by putting in the actual scores but unfortunately we always use W & L in our results grids as this type of template is used for different competitions involving anything from 6 to 12 teams and so it doesn't look correct when you have a number in your new grid as at first glance you can't look along a row and just see how many Wins or Losses if you see what I mean in other words this grid will be used by a number of different umpires and they are going to say to me it's not what we are used to.

I do understand that it's easier for you to design a system using scores and what a tremendous lot of work you have put into this latest sample but no unfortunately it's not quite right yet, so sorry and this time I did get an email. Alan

Many thanks, let me have a good look at it and I will get back to you. Alan

Thanks again for your clever idea and sorry to be slow in coming back to you but it is starting to get a bit too complicated and I don't think it is worth it going down this route

In an earlier file (V7?) you put in actual scores and I think you said that worked apart, from x = #value, so I wondered if instead of saying JPN beat SUI 7-5 why can't we put in JPN 1 SUI 0 as I said earlier in other words a 1=W & 0=loss which is the same thing as putting in the score, would that work?

I will try that out in the next few hours by changing scores in V7 to 1 or 0 and see what happens and upload it to you if you think this might work. Please let me know what you think. Alan

OK I think you have gone as far as you can to automate the process and I would thank you very much for the incredible amount of work you have put into trying to solve this problem and I will now mark it as solved and perhaps I might be able to contact you for help in the future. Regards