Over the next 10 matchdays you are the data analyst for the Jetball Pro League, a made-up esports league of 8 rocket-ball clubs. Every round of results drops into your Excel workbook, and your formulas turn it into a ladder that sorts itself, player stats, a fantasy squad and a live dashboard for Grand Final Night.
league-firstname.xlsx on your Desktop (it follows you to any laptop). Save it as an Excel Workbook, never as CSV, and press Ctrl+S often.Need to log into Minecraft Education, Canva, or any other app that asks you to sign in? Most school and app accounts use the same login: your username is [email protected] (add a number if your name needs one, e.g. firstname.lastname2), and your password is the same one you use to log into your school computer.
Every project has at least one thing to hand in. Log into connect.det.wa.edu.au with your firstname.lastname and computer password, then head to your class and open the Submission section. Handing in more than one file? Zip them together first - total size must stay under 50MB.
Build one Excel workbook that tracks a whole esports season: results in, ladder, stats, fantasy squad and dashboard out, all updating by formula, then present it at Grand Final Night.
| Criteria | Rookie (0-1) |
Pro (2-3) |
All-Star (4) |
MVP (5) |
Score |
|---|---|---|---|---|---|
| Formulas | Values typed in by hand, or formulas that give errors | Most formulas work, some ranges not locked | Results, Ladder, Standings and Players all work by formula and update with each round | Every formula correct, and you can explain how $ and each function work | / 5 |
| Data Display | No formatting or charts | Some formatting or one chart | Conditional formatting rules and two titled charts that suit the data | Clear, consistent design a viewer reads at a glance | / 5 |
| Analysis | No fantasy squad, or picks with no reason | Squad under the cap with some reasons | Squad picked using rates such as goals per game and accuracy | Picks justified with evidence and reviewed against the results | / 5 |
| Dashboard & Talk | No dashboard or talk | Dashboard shows some facts, talk reads from notes | Dashboard updates by formula, 2-minute talk uses the charts | Dashboard tested with new data, talk tells the season's story with numbers | / 5 |
| Total | / 20 | ||||
Every file the season needs. Each one downloads a .csv to your Downloads. Results and stats files hold everything so far, so you paste each new one over the old data.
jpl-results-r1.csv. Excel shows it in a gridleague-firstname.xlsx. A CSV cannot keep formulas, so this step mattersleague-firstname.xlsx from the Desktop, and ends with Ctrl+S.= and works something out from other cells. =D2+E2 adds the two scores in row 2IF makes a decision: IF(test, value if true, value if false). The Winner formula asks two questions: did Blue score more, or did Orange score more? If neither, it is a DrawA2=""=IF(A2="","",IF(D2>E2,B2,IF(E2>D2,C2,"Draw")))
=IF(A2="","",D2+E2), fill it down to row 60. Which match had the most goals?COUNTIF(range, value) counts the cells in a range that match. Wins are easy: count how many times the club's name appears in the Winner columnCOUNTIFS counts rows where every condition is true: a draw is a row where the club played and the Winner says Draw$ signs lock a range so it stays put when you fill down: Results!$F$2:$F$60 never moves. A2 has no $, so each row looks up its own club=COUNTIF(Results!$B$2:$B$60,A2)+COUNTIF(Results!$C$2:$C$60,A2)
=COUNTIF(Results!$F$2:$F$60,A2)
=COUNTIFS(Results!$B$2:$B$60,A2,Results!$F$2:$F$60,"Draw")+COUNTIFS(Results!$C$2:$C$60,A2,Results!$F$2:$F$60,"Draw")
=B2-C2-D2
=3*C2+D2
$.=IFERROR(ROUND(C2/B2*100,0),0). Which club wins most often?SUMIF(range, value, sum range) adds up only the rows that match. A club's goals are its scores as Blue plus its scores as Orange. Paste GF and GA into F2 and G2, GD into H2RANK.EQ gives each Score its place: 1 for the biggest. The COUNTIF on the end splits an exact tie so no two clubs share a place. Type Score in J1 and Pos in K1, paste both, then fill F2:K2 down to row 9MATCH finds which row holds position 1, and INDEX returns that row's value. Paste the formula into B2, fill right to J2, then fill B2:J2 down to row 9=SUMIF(Results!$B$2:$B$60,A2,Results!$D$2:$D$60)+SUMIF(Results!$C$2:$C$60,A2,Results!$E$2:$E$60)
=SUMIF(Results!$B$2:$B$60,A2,Results!$E$2:$E$60)+SUMIF(Results!$C$2:$C$60,A2,Results!$D$2:$D$60)
=F2-G2
=I2*10000+H2*100+F2
=RANK.EQ(J2,$J$2:$J$9)+COUNTIF($J$2:J2,J2)-1
=INDEX(Ladder!A$2:A$9,MATCH($A2,Ladder!$K$2:$K$9,0))
Ladder!A$2:A$9 the row numbers are locked but the column is not, so filling right moves A to B to C. $A2 is the opposite: the column is locked, the row moves. That is a mixed reference.SUMIF: add up column D of Stats for rows with that player's nameStats!D$2:D$400 moves to E, F and G (assists, saves, shots) because only the rows are locked. Then fill E2:I2 down to row 33MAX finds the most goals and INDEX with MATCH finds whose they are=COUNTIF(Stats!$B$2:$B$400,$A2)
=SUMIF(Stats!$B$2:$B$400,$A2,Stats!D$2:D$400)
=IFERROR(ROUND(F2/E2,2),0)
=IFERROR(ROUND(F2/I2*100,0),0)
=INDEX($A$2:$A$33,MATCH(MAX($F$2:$F$33),$F$2:$F$33,0))
=INDEX($A$2:$A$33,MATCH(MAX($G$2:$G$33),$G$2:$G$33,0))=$A2<=4, click Format and choose a green fill. OK=$A2>=7 and a red fill. The $ locks column A, so every cell in the row checks its positionIFERROR keeps an empty pick blank instead of showing an error=IFERROR(INDEX(Players!$B$2:$B$33,MATCH(B2,Players!$A$2:$A$33,0)),"")
=IFERROR(INDEX(Players!$D$2:$D$33,MATCH(B2,Players!$A$2:$A$33,0)),"")
=SUMIFS(Stats!D$2:D$400,Stats!$B$2:$B$400,$B2,Stats!$A$2:$A$400,">=6")
=E2*4+F2*3+G2
=SUM(D2:D6)
=IF(D8>50,"OVER CAP","OK")
=(E2*4+F2*3+G2)*IF(I2="C",2,1), then fill it down to row 6.B$1 locks the club row, $A2 locks the round column. Fill right to I2, then fill B2:I2 down to row 8=3*COUNTIFS(Results!$F$2:$F$60,B$1,Results!$A$2:$A$60,"<="&$A2)+COUNTIFS(Results!$B$2:$B$60,B$1,Results!$F$2:$F$60,"Draw",Results!$A$2:$A$60,"<="&$A2)+COUNTIFS(Results!$C$2:$C$60,B$1,Results!$F$2:$F$60,"Draw",Results!$A$2:$A$60,"<="&$A2)
=Standings!B2
=Players!M2
=MAX(Players!$F$2:$F$33)
=INDEX(Players!$A$2:$A$33,MATCH(MAX(Players!$H$2:$H$33),Players!$H$2:$H$33,0))
=Fantasy!H8
=IF(A2="","",IF(D2>E2,B2,IF(E2>D2,C2,"Draw")))
=Finals!F4
After 10 matchdays of formulas, rankings, colour rules and charts, the finals results drop live. Your dashboard crowns the champion, your fantasy squad gets its last points, and you take the Analyst Desk for two minutes.
Download Round 1, open it in Excel // Matchday 01