Missing cells in football league table

[Potsdamerplatz]

Hello again.

I am wondering if a few of you guys might be able to help me solve a problem in a football league table which I have designed. The table updates itself automatically when results are entered.

However, there is a problem when teams are stuck on the same level of points and when this happens I get the #N/A error.

Fortunately, I know what the problem is. I just can't seem to sort it although others who have used the same template have been able to do so. I must be doing something wrong.

When you load up the spreadsheet everything looks fine. Barcelona and Manchester United have both won their matches in Group A and the table is working correctly.

But try changing the results to a couple of 1-1 draws, or 0-0 draws and you will see the error messages appear on the league table.

The reason for this is that the guy who designed the template has each club ranked.

(1) Points
(2) Goal Difference
(3) Goals scored
(4) Away goals

If you take a look at the worksheet named "Dummy" you will see this in cells C4:F4

When clubs share the same amount of ranking points (A4:A7) the error message appears as there is no other formula for choosing 1st, 2nd, 3rd and 4th in each group.


Someone else had the exact same problem and here is what they wrote on the designer's blog:

There is one small problem that i have found and that is if the clubs are still ranked in the same position after all formulas have been used an error occurs where clubs are missing and N/A appears in the box.

and this was the reply:

If you want them to share the same position, the easiest way is add new column in the league table worksheet next to position column, and put “if function” to compare your criteria between two rows. If your criteria is met, get the position information and make those two rows position the same.

Now this just goes completely over my head, but maybe you guys will make some sense of it. All attempts at following the above have been unsuccessful and still result in error messages.

Ideally, when the worksheet loads up and is completely blank before results are entered I would like to have the clubs ranked:

1. Barcelona
2. Manchester United
3. Galatasaray
4. IFK Gothenburg

(as can be seen on the worksheet titled Teams)


And if possible, I would like to have this hidden "IF" formula on the dummy worksheet so that it doesn't clutter up the Groups page as free space is already tight. Once the problem is fixed I will be hiding the dummy sheet and working solely on the Groups worksheet.

Alternatively, if this is not possible there is a 2nd solution again posted on the designer's blog which would see all clubs ranked together placed in alphabetical order:

Question - Greetings and thanks for a wonderful tutorial. I have used it to create my own EPL spreadsheet. Here is the problem I have. Say two(or three) teams are equal in points, goal difference and goals scored. Now I want to place (rank) those equal teams based on Alphabet. I have read and read many examples, but I can not figure it out.

and the designer's reply:

The easiest way is just to put the coefficient number based on the order of alphabet of those 20 teams and put it into position calculation.


All help getting this sorted would be appreciated. Although I've used the designer's own tutorial to get this up and running, I've spent hours fine tuning the details for Champions League format rather than the traditional 20-team English Premier League.

I feel like a racing driver who has broken down on the final lap with the chequered flag in sight.

If you need any further information please just let me know. Good luck guys!!!

[JimmyA]

Hi There

I think I have managed to part way answer your question.

In the range A4:A7 I have placed a new formula which will produce a unique rank order. So if there are 2 teams both with 3 then it will keep the first in the list as 3 and make the next one 4. These formula are "Array Formulas" which means if you edit them you have to press CTRL+SHIFT+ENTER and not just ENTER. If it has worked you will see { } around the formula (you can’t just put them in yourself.)

I have moved your original formula which SUMs the results to column AE, this is what the new formula looks at so needs to be kept there.

I have put the reverse sort version of new formula in range AG4:AG7 just in case you needed it the other way round.

I say part way answered your question because I have got the formula off a web site and adapted it but I can’t for the life of me figure out how it works, it just does!! So if anyone else can explain that would be great!!!!

http://support.microsoft.com/kb/152567

One final thing, are you sure it is producing the correct values from the original SUM Formula (now in AE4:AE7)? Is just using SUM ok to work goal difference etc........

Hope this helps

Jim

[Potsdamerplatz]

Hi Jim and thanks again for your help. You are a lifesaver.

I've spend hours on this using several different templates and just couldn't get it to work. Looks like you've cracked it though so well done.

Goal difference is only one way of deciding who tops the group should all teams be equal on points. If goal difference is identical (D4:D7) then goals scored is used (E4:E7) and then finally away goals (F4:F7)

The formula you added prevents any team from sharing the same rank (A4:A7) which is why I was getting the N/A error messages before.

I'll give it a try over the next few days and get back to you if there are any problems.

I've already completed tables for the English league going back to 1987, but it was the European tables I was having problems with as there are at least 4 mini-tables each season instead of just one large table of 20 teams. Hence, the difficulties.

Thanks again.