Which two teams qualify to next stage? [Excel Homework]

Excel Challenges - 16 comments

Time for some soccer fun in spreadsheets. Given a team points table like below:

Which teams qualify for next stage

How would you figure out in Excel, which teams qualify for next stage. The rules for this exercise are,

  • Pick the top two teams by points
  • If there is a tie, use Goal Difference (GD) to break ties (more GD is good)
  • If there is a tie, use Goals For (GF) to break ties

Note: these rules are only a sub-set of actual FIFA rules. But hey, we are not running soccer competition here. Just having fun, so let’s keep it simple.

In the above example, your answer should be 1 & 2 against Brazil and Mexico in column G.

Download this example points tables and use it to figure out the solution.

You may use formulas, VBA or Power Query to figure out the answers. If using Power Query, assume the input is just a 4 row table with column names, as shown above.

Go ahead and post your answers in the comments section. I am waiting to see all creative ways to attack this problem.

Want more soccer fun?

More Excel challenges & homework problems

 

Chandoo

Hello Awesome...

My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.

I hope you enjoyed this article. Visit Excel for Beginner or Advanced Excel pages to learn more or join my online video class to master Excel.

Thank you and see you around.

Related articles:

Written by Chandoo
Tags: , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

16 Responses to “Which two teams qualify to next stage? [Excel Homework]”

  1. ABC says:

    You need just one additional auxiliary column with this formula.
    =RANK(C18,C18:C21)+SUMPRODUCT(--(D18<D18:D21))/10+SUMPRODUCT(--(E18<E18:E21))/100

    The first RANK will divide everyone into tiers. and the two sumproduct will help divide the tiers depending on the tiebreaker.

    The first sumproduct performs a ranking of the goal difference (the "--" is a double unary negation which forces the inner array to "0s" and "1s" to be added. And the result of the Array is the number of goal difference entries smaller than the one being tested.
    By dividing by 10, we force it to be a number smaller than 1, therefore whatever result happens, it cannot change the tier. If you have more ten or more, then you might need to divide by 100

  2. hocine satour says:

    Array formula :
    = IF(C4*1000+D4*100+E4*10=MAX($C$4:$C$7*1000+$D$4:$D$7*100+$E$4:$E$7*10),1, IF(C4*1000+D4*100+E4*10=LARGE($C$4:$C$7*1000+$D$4:$D$7*100+$E$4:$E$7*10,2),2,""))

  3. Kolyu says:

    @ABC: your formula is interesting but gives just half of the result.
    It returns some unique result for each team that needs to be further ranked in order to see who qualifies.

    My proposal is to use helper column with the following formula:
    =Points+GD*0.0001+GF*0.000001
    (column headings are used instead of Excel references)

    This formula calculates unique points for each team that then are ranked using Rank formula

  4. Irina says:

    Array formula:
    ={SUM(IF((C4*100+D4*10+E4)>=($C$4:$C$7*100+$D$4:$D$7*10+$E$4:$E$7);0;1))+1}

  5. Xiq says:

    Nice!

    I modified my solution a bit after I saw your method. And I didn't the IF function. I used SUMPRODUCT so that you don't have to worry about ARRAY entry

    =SUMPRODUCT(1-(C$4:C$7+D$4:D$7/10+E$4:E$7/100<=C4+D4/10+E4/100))+1

    • Xiq says:

      Here is a version in Table reference format and a blank result if it's not position 1 or 2.

      =CHOOSE(SUMPRODUCT(1-([Points]+[GD]/10+[GF]/100<=[@Points]+[@GD]/10+[@GF]/100))+1,1,2,"","")

    • Rob T says:

      I really like this solution, but I think there are two problems:

      1: There needs to be more separation between the magnitudes (by which I mean the /10 and /100 parts).

      Consider the case (leaving aside whether this is likely or even possible!):
      Team A: 6pts, GD+7, GF:19
      Team B: 6pts, GD+8, GF:8

      The GF for Team A (being >10) carries over to create an apparent GD of 8 (the same as Team B's REAL GD). The remainder (9) is greater than Team B's GF (8) and so Team A is incorrectly ranked higher than Team B.

      2: A similar problem could be caused by negative GD. For example (again, reality notwithstanding):

      Team X: 3pts, GD-3, GF:4
      Team Y: 2pts, GD+8, GF:8

      Team X is ranked lower than Team Y because the negative GD impacts on the "points" part of the calculation.

      I THINK both these problems would be solved by ensuring greater separation (i.e. /100 and /10000), but to be extra safe, I would ensure GD element is positive first by adding (say) 50, thus:

      =SUMPRODUCT(1-(C$4:C$7+((D$4:D$7+50)/100)+E$4:E$7/10000<=C4+((D4+50/100)+E4/10000))+1

      And applying Xiq's table references and blank results:

      =CHOOSE(SUMPRODUCT(1-([Points]+(([GD]+50)/100)+[GF]/10000<=[@Points]+(([@GD]+50)/100)+[@GF]/10000))+1,1,2,"","")

  6. Ajit says:

    =IF(AND(COUNTIF($C$4:$C$7,MAX($C$4:$C$7))=1,C4=MAX($C$4:$C$7)),1,IF(AND(COUNTIF($D$4:$D$7,MAX($D$4:$D$7))=1,D4=MAX($D$4:$D$7),C4=MAX($C$4:$C$7)),1,IF(AND(E4=MAX($E$4:$E$7),C4=MAX($C$4:$C$7),D4=MAX($D$4:$D$7)),1,2)))

  7. Jozef says:

    Power Query:

    1. Sort descending based on Points, GD and GF

    2. Add index column starting from 1

    Formula:
    I like the solution of Irina above.
    ={SUM(IF((C4*100+D4*10+E4)>=($C$4:$C$7*100+$D$4:$D$7*10+$E$4:$E$7);0;1))+1}

  8. Prince Goyal says:

    {=MATCH(TRUE,($C39+$D39+$E39=LARGE($C$39:$C$42+$D$39:$D$42+$E$39:$E$42,ROW($A$1:$A$4))),0)}

  9. MichaelCH says:

    =CHOOSE(SUM(N(MMULT(10^{4,2,0},TRANSPOSE($C$4:$E$7))>=SUM(C4:E4*10^{4,2,0}))),1,2,"","")

  10. =RANK.EQ($C4,$C$4:$C$7,0)+COUNTIFS($C$4:$C$7,$C4,$D$4:$D$7,">"&$D4)

    Should work

  11. =RANK.EQ($C4,$C$4:$C$7,0)+COUNTIFS($C$4:$C$7,$C4,$D$4:$D$7,">"&$D4)

    Should work

  12. Ayushman says:

    Hi,

    Why not this formula (Non-Array)?

    =RANK(C4,$C$4:$C$7)+SUMPRODUCT(--($C$4:$C$7=C4)*($D$4:$D$7>D4))+SUMPRODUCT(--($D$4:$D$7=D4)*($E$4:$E$7>E4))

Leave a Reply


« »