FOOT Table in Excel with multiple conditions. Solved

Question

Hello,  I created an Excel table that allows me to automatically rank football teams based on several criteria: points / best goal difference / goals scored / goals conceded. I would like to keep these criteria while adding a new ranking method. In the case where several teams have the same number of points, they would be ranked first by the points obtained in their encounters with each other, then by the largest goal difference between them (goals scored / goals conceded), and finally by the highest number of goals scored in their meetings (there are 8 ranking methods!). I am attaching 3 Excel sheets where (Sheet 1) contains the rankings and the matches played or not. I have highlighted in yellow the match FRANCE - ITALY won by FRANCE 3 to 2. The ranking in my table does not take into account the special cases during encounters between teams with the same number of points. (FRANCE should be ahead of ITALY) Sheet 2 allows me to compile the data, and Sheet 3 includes the "famous" rules that are problematic!!!  Thank you!!! Best regards.  https://www.cjoint.com/c/EKbsixzVtkJ

via55 · Answer

Hello Yvon  What version of Excel do you have? 2003 or a later version? In the latter case, I might have a solution with the SUMIFS function. In the former case, we’ll need to use SUMPRODUCT, but I think we'll need other intermediate tables.  Awaiting your clarification  Best regards Via  --  "Imagination is more important than knowledge." A. Einstein

yvon1351 · Answer

Hello Via55  First of all, thank you for taking an interest in my problem. I have a 2003 version (old computer with XP!) I also think that it will be necessary to go through intermediate tables to achieve the desired result. Since I'm not an Excel formulas specialist, I won't be much help to you. I don't know if you keep up with football news, but what I'm looking for corresponds to the Champions League standings and not the Ligue 1 standings, which is a different calculation method that I am familiar with. Thanks again for your help, and see you soon.  Best regards.

via55 · Answer

Hello,  Okay, I'll look into it, it's going to take a bit of time I'll get back to you as soon as possible  Best regards Via  -- "Imagination is more important than knowledge." A. Einstein

yvon1351 · Answer

Re,  Okay! Take your time! Thank you!!!  Best regards

via55 · Answer

Re  Check if this works for you and if it's correct http://www.cjoint.com/c/EKcpllwZqwX  I explained the general procedure on sheet 2; if you have questions about the formulas, just ask me  Best regards Via  -- "Imagination is more important than knowledge." A. Einstein

yvon1351 · Answer

Re  Well done Via55! That seems perfect to me! I will take back one of my tables that didn't have this calculation method and was therefore incorrect. This will allow me to see if it corrects the error. Regarding points A and B of the regulations included in the table, do you think I can continue working on points CDE by myself while following your logic? In point 3, you mention: handle up to 3 ties. Is it possible to extend this number? Can this table with 6 teams be used with a varying number of teams (4-5 or 8 teams)? Regarding the formulas, I was not aware of these ways to use them. Impressive! Thank you again for your valuable help.  Best regards

via55 · Answer

Hi,  Glad to hear you're okay with it! 1) Regarding the following points, with the right criteria for SOMMEPROD, you should be able to pull other tables from the results displayed vertically.  2) To handle 3 ties, since the teams are ranked in descending order, you need to compare each team with the 2 previous ones and the 2 following ones in the list (the ties can be found within this range), hence the 4 IF statements that are added together in the formula =IF(C11=C12;INDEX($C$19:$H$24;MATCH($B11;$B$19:$B$24;0);MATCH($B12;$C$18:$H$18;0)))+IF(C11=C10;INDEX($C$19:$H$24;MATCH($B11;$B$19:$B$24;0);MATCH($B10;$C$18:$H$18;0)))+IF(C11=C13;INDEX($C$19:$H$24;MATCH($B11;$B$19:$B$24;0);MATCH($B13;$C$18:$H$18;0)))+IF(C11=C9;INDEX($C$19:$H$24;MATCH($B11;$B$19:$B$24;0);MATCH($B9;$C$18:$H$18;0))) The more you want to increase the number of ties, the longer the formula will need to be!! (6 IFs for 4 ties, 8 for 5, etc.)  3) You can of course extend the table to more teams by also enlarging the other two-dimensional tables below.  Good luck  Best regards  Via  -- "Imagination is more important than knowledge." A. Einstein

yvon1351 · Answer

Hello Via55,  I have redone my table that was causing problems using your method and it works perfectly. I will follow your advice and see if I can extend the formulas. I am taking advantage of your knowledge to present a last "issue" this time with the INDIRECT formula. It has nothing to do with the previous one, but if you could help me find the solution, that would be great. I am attaching the example with my explanations.  Thank you and have a nice day.  https://www.cjoint.com/c/EKdiBfbdnp5

via55 · Answer

Hello Yvon  No need to use INDIRECT, rather use INDEX combined with MATCH which searches for a value in a given array that corresponds to another array For example, for your table on sheet 2, by entering the 1st club in D5, you will get the 2nd in G5 with the formula: =IF(ISERROR(VLOOKUP(D5;D9:G18;4;0));INDEX(D9:D18;MATCH(D5;G9:G18;0));VLOOKUP(D5;D9:G18;4;0))  Have a nice day too  Best regards Via  -- ""Imagination is more important than knowledge." A. Einstein

yvon1351 · Answer

Hi,  I've noted all these formulas and thank you once again for your help.  Best regards.

FOOT Table in Excel with multiple conditions.

10 answers

Smart tech tv without remote control

How to reset a gps on a 2010 automatic c4 picasso clocking?

I no longer have the scrollbar in the right margin

Doctolib plans to use your health data for its ai research

Google maps does not recognize my current location [pc]

Understanding the use of the 'i' compartment in the washing machine

No network inside the house.

Installation of the claude ai

Missing lock icon

Texture issues in enshrouded