Menu
Which two teams qualify to next stage? [Excel Homework]
Excel Challenges - 17 comments
Time for some soccer fun in spreadsheets. Given a team points table like below:
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
- Calculate the employee bonus
- Does this number have repetitive digits
- What is the most frequent item
- How many Friday the 13ths are in a year?
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:
|
Leave a Reply
| « Excel Tables Tutorial & 13 Tips for making you a Data Guru | How to undo in Power Query [Quick Tip] » |
At Chandoo.org, I have one goal, "to make you awesome in Excel & Power BI". I started this website in 2007 and today it has 1,000+ articles and tutorials on data analysis, visualization, reporting and automation using Excel and Power BI. 
17 Responses to “Which two teams qualify to next stage? [Excel Homework]”
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
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,""))
@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
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}
nice solution!
Great!
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
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,"","")
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,"","")
=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)))
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}
{=MATCH(TRUE,($C39+$D39+$E39=LARGE($C$39:$C$42+$D$39:$D$42+$E$39:$E$42,ROW($A$1:$A$4))),0)}
=CHOOSE(SUM(N(MMULT(10^{4,2,0},TRANSPOSE($C$4:$E$7))>=SUM(C4:E4*10^{4,2,0}))),1,2,"","")
=RANK.EQ($C4,$C$4:$C$7,0)+COUNTIFS($C$4:$C$7,$C4,$D$4:$D$7,">"&$D4)
Should work
=RANK.EQ($C4,$C$4:$C$7,0)+COUNTIFS($C$4:$C$7,$C4,$D$4:$D$7,">"&$D4)
Should work
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))
Try to get this to work...
=RANK(($C4&RANK($D4,$D$4:$D$7,1)&RANK($E4,$E$4:$E$7,1))*1,($C4:$C7&RANK($D4:$D7,$D$4:$D$7,1)&RANK($E4:$E7,$E$4:$E$7,1))*1,0)
Can somebody please help, thank you.