Home
// Digital Technology - Year 9 & 10 //

SPREADSHEET
SPORTS LEAGUE

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.

📊 Excel Formulas
🚀 Jetball League
🎯 Fantasy Draft
🏆 Grand Final
Analyst Kit
📊
Microsoft Excel
The desktop Excel app on your laptop. Every formula on this page works in any Excel from 2016 on. Click Copy on a formula, click in Excel's formula bar, Ctrl+V, Enter.
🚀
The League
The Jetball Pro League: 8 made-up clubs, 3v3 rocket-ball, 32 players. Each club plays every other club once over 7 rounds, then the top 4 play the finals.
⬇️
Data Drop
Each matchday's buttons download that round's results as a .csv to your Downloads. Missed a lesson? Every file is in the League Data Drop below.
💾
Saving
One workbook all season: 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.
⚠ A CSV keeps no formulas. Always Save As .xlsx first.
🧠
AI Assistant
copilot.microsoft.com - ask it to explain a formula one part at a time. Microsoft's Excel functions list explains every function.
⚠ AI can invent functions or mix up arguments - test every formula against the data!
🏆
Grand Final Night
The finals results drop live. Your dashboard crowns the champion, then you give a 2-minute analyst talk. Awards: Best Dashboard, Sharpest Analyst, Fantasy Champion and People's Choice.
// Primary Objective

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.

Calculate wins, points and stats with IF, COUNTIFS and SUMIF
Rank the ladder with tie-breakers using RANK.EQ, INDEX and MATCH
Show the story with conditional formatting and charts
Analyse players to draft a fantasy squad, then present your dashboard
// Marking Key & Rubric
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
Scroll sideways to see every column
// League Data Drop

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.

Results

Player Stats

Starter Sheets

Grand Final Night

Season Log
Week 1
01
🚀 Kickoff
▾
// MATCHDAY OBJECTIVEThe Round 1 results downloaded, opened in Excel, saved as an Excel Workbook on your Desktop, and the sheet renamed Results.
  • Welcome to the Jetball Pro League: 8 fictional clubs playing 3v3 rocket-ball. You are the league's data analyst. Every lesson a new round of results drops, and your workbook turns them into a ladder, player stats, a fantasy draft and a dashboard
  • Click Results to Round 1 below. A .csv file saves to your Downloads. CSV means comma separated values: plain text, one row per line, a comma between columns
  • Open Excel. File, then Open, then Browse, go to Downloads, choose Text Files in the file type list and open jpl-results-r1.csv. Excel shows it in a grid
  • Tour the grid: columns have letters, rows have numbers, and a cell is a column and a row, like B2. The headings are in row 1: Round, Blue, Orange, BlueGoals, OrangeGoals
  • Save it properly: File, then Save As, then Browse to your Desktop. In Save as type choose Excel Workbook (*.xlsx) and name it league-firstname.xlsx. A CSV cannot keep formulas, so this step matters
  • Double-click the sheet tab at the bottom and rename it Results. Make the headings bold and widen the columns so every name shows. Save with Ctrl+S
💡 HintYour Desktop follows you to any laptop. Every lesson starts by opening league-firstname.xlsx from the Desktop, and ends with Ctrl+S.
// ASK AI"What is a CSV file, and why does Excel warn that some features can be lost if I save a workbook as CSV?"
02
🧮 Results Sheet
▾
// MATCHDAY OBJECTIVERound 2 pasted into your Results sheet and a Winner column that names the winning team or Draw for every match, filled down to row 60.
  • Download Results to Round 2. Each drop holds every result so far, so you replace the old data. Paste it in: open the new file in Excel (File, then Open, then Browse to Downloads), click A1, press Ctrl+A once (just the data) then Ctrl+C, click your own workbook on the taskbar, click the Results tab, click cell A1 and press Ctrl+V. Close the downloaded file without saving
  • A formula starts with = and works something out from other cells. =D2+E2 adds the two scores in row 2
  • IF 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 Draw
  • Type Winner in F1. Click the cell, click Copy below, click in the formula bar above the grid, press Ctrl+V, then Enter. Click F2 and check it names a team
  • Fill it down: select F2, then drag the small square at its bottom right corner (the fill handle) down to row 60. F3 now checks D3 and E3, and so on: a relative reference. Rows with no match stay blank because the formula first checks A2=""
  • Check your Winner column against the scores by eye, then Ctrl+S
// Results F2: Winner
=IF(A2="","",IF(D2>E2,B2,IF(E2>D2,C2,"Draw")))
💡 HintPress Ctrl+A only once in the downloaded file. Pressed three times it selects the whole sheet, and pasting that over your Results sheet would wipe your Winner formulas.
🔍 Google Image Search"excel if formula example"
// ASK AI"Explain this Excel formula one part at a time: =IF(D2>E2,B2,IF(E2>D2,C2,"Draw"))"
// LEVEL UPAdd a Total column in G: =IF(A2="","",D2+E2), fill it down to row 60. Which match had the most goals?
Week 2
03
🪜 The Ladder
▾
// MATCHDAY OBJECTIVEA Ladder sheet with Played, Won, Drawn, Lost and Points for all 8 clubs, worked out by COUNTIF and COUNTIFS formulas with locked ranges.
  • Download Results to Round 3 and paste it into your Results sheet (A1, Ctrl+A once, Ctrl+C, Results tab, A1, Ctrl+V). Your Winner formulas already reach row 60, so the new rows get a winner straight away
  • Click the New Sheet plus button at the bottom and rename the new tab Ladder. Download the Ladder Starter and paste it into A1: headings plus the 8 club names
  • 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 column
  • COUNTIFS counts rows where every condition is true: a draw is a row where the club played and the Winner says Draw
  • The $ 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
  • Paste each formula below into row 2 (Click the cell, click Copy below, click in the formula bar above the grid, press Ctrl+V, then Enter), then select B2:I2 and fill down to row 9. Points are 3 for a win and 1 for a draw
// Ladder B2: Played (P)
=COUNTIF(Results!$B$2:$B$60,A2)+COUNTIF(Results!$C$2:$C$60,A2)
// Ladder C2: Won (W)
=COUNTIF(Results!$F$2:$F$60,A2)
// Ladder D2: Drawn (D)
=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")
// Ladder E2: Lost (L)
=B2-C2-D2
// Ladder I2: Points (Pts)
=3*C2+D2
💡 HintCheck one club by hand: count its wins in the Results sheet and compare with your formula. If they differ, look for a missing $.
// ASK AI"What is the difference between COUNTIF and COUNTIFS in Excel? Give a sports example."
📤 Submit via ConnectYour workbook (.xlsx) with the Results and Ladder sheets
// LEVEL UPAdd a Win % heading in M1 and this in M2, then fill down: =IFERROR(ROUND(C2/B2*100,0),0). Which club wins most often?
04
📈 Sort & Rank
▾
// MATCHDAY OBJECTIVEGoals for, goals against, goal difference and a ranking score on the Ladder, and a Standings sheet that puts the clubs in order by itself.
  • Download Results to Round 4 and paste it into Results
  • 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 H2
  • League rule for ties: points first, then goal difference, then goals for. The Score formula folds all three into one number, so the biggest score is the leader
  • RANK.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 9
  • Add a sheet called Standings and paste the Standings Starter into A1. MATCH 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
  • Freeze the headings: click A2 on Standings, then View, then Freeze Panes, then Freeze Panes. Now change a score in Results and watch Standings re-order. Press Ctrl+Z to undo it
// Ladder F2: Goals For (GF)
=SUMIF(Results!$B$2:$B$60,A2,Results!$D$2:$D$60)+SUMIF(Results!$C$2:$C$60,A2,Results!$E$2:$E$60)
// Ladder G2: Goals Against (GA)
=SUMIF(Results!$B$2:$B$60,A2,Results!$E$2:$E$60)+SUMIF(Results!$C$2:$C$60,A2,Results!$D$2:$D$60)
// Ladder H2: Goal Difference (GD)
=F2-G2
// Ladder J2: Score
=I2*10000+H2*100+F2
// Ladder K2: Position
=RANK.EQ(J2,$J$2:$J$9)+COUNTIF($J$2:J2,J2)-1
// Standings B2: Standings (fill right and down)
=INDEX(Ladder!A$2:A$9,MATCH($A2,Ladder!$K$2:$K$9,0))
💡 HintIn 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.
// ASK AI"Why would a sports ladder use goal difference to separate clubs on the same points?"
// LEVEL UPTest the tie-breaker: find two clubs on the same points, change one result in Results so their goal difference swaps, and check Standings swaps them. Then press Ctrl+Z to put the real score back.
Week 3
05
🎮 Player Stats
▾
// MATCHDAY OBJECTIVEYour Players sheet showing every player's games, goals, assists, saves, shots, goals per game and shot accuracy, plus the league's top scorer found by formula.
  • Download Results to Round 5 and paste it into Results. Then add a sheet called Stats, download Player Stats to Round 5 and paste it in: one row for each player in each match
  • Add a sheet called Players and paste the Players Starter into A1: all 32 players with their club, role and fantasy price
  • Games is how many rows a player has in Stats. Goals uses SUMIF: add up column D of Stats for rows with that player's name
  • Fill F2 right to I2: Stats!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 33
  • Totals can mislead: a player who missed games has fewer goals. Goals per game and Accuracy % (goals out of shots) are fairer. Paste J2 and K2 and fill down
  • Type Top scorer in L2 and paste the formula into M2. MAX finds the most goals and INDEX with MATCH finds whose they are
// Players E2: Games
=COUNTIF(Stats!$B$2:$B$400,$A2)
// Players F2: Goals (fill right to I2)
=SUMIF(Stats!$B$2:$B$400,$A2,Stats!D$2:D$400)
// Players J2: Goals per game
=IFERROR(ROUND(F2/E2,2),0)
// Players K2: Accuracy %
=IFERROR(ROUND(F2/I2*100,0),0)
// Players M2: Top scorer
=INDEX($A$2:$A$33,MATCH(MAX($F$2:$F$33),$F$2:$F$33,0))
💡 HintTwo players on the same top total? MATCH returns the first one in the list. Look at the column to check for a tie before you announce a winner.
🔍 Google Image Search"esports player stats table"
// ASK AI"Why is goals per game a fairer way to compare players than total goals?"
📤 Submit via ConnectYour workbook with the Players sheet and its top scorer
// LEVEL UPFind the assist leader: type Top assists in L3 and put this in M3: =INDEX($A$2:$A$33,MATCH(MAX($G$2:$G$33),$G$2:$G$33,0))
06
🎨 Colour Codes
▾
// MATCHDAY OBJECTIVEConditional formatting that turns the top 4 in Standings green and the bottom 2 red, data bars on goals, and the top 3 assist makers highlighted.
  • Conditional formatting changes how a cell looks when a rule is true, and it updates by itself when the data changes
  • Finals spots: on Standings select A2:J9. Home, then Conditional Formatting, then New Rule, then Use a formula to determine which cells to format. Type =$A2<=4, click Format and choose a green fill. OK
  • Danger zone: same steps with =$A2>=7 and a red fill. The $ locks column A, so every cell in the row checks its position
  • On Players select F2:F33. Home, then Conditional Formatting, then Data Bars, and pick a colour. Longer bar, more goals
  • Select G2:G33 (assists). Home, then Conditional Formatting, then Top/Bottom Rules, then Top 10 Items. Change 10 to 3
  • Check every rule still works: change a score in Results, watch the colours move, then Ctrl+Z
💡 HintMade a mistake? Home, then Conditional Formatting, then Manage Rules shows every rule on the sheet so you can edit or delete it.
// ASK AI"When does conditional formatting help a reader, and when does too much colour make a table harder to read?"
// LEVEL UPSelect K2:K33 (Accuracy %) on Players. Home, then Conditional Formatting, then Icon Sets. Do the icons agree with the top scorer list? Who is the most accurate shooter?
Week 4
07
🎯 Fantasy Draft
▾
// MATCHDAY OBJECTIVEA Fantasy sheet with 5 players picked from drop-down lists, their club and price filled in by formula, and a cap check that shows OK at 50 credits or less.
  • Draft rules: pick 5 players from any clubs. Prices are on your Players sheet and your squad must cost 50 credits or less. Fantasy points count from Round 6, through the finals: goal 4, assist 3, save 1
  • Add a sheet called Fantasy and paste the Fantasy Starter into A1
  • Drop-down lists: select B2:B6. Data, then Data Validation. In Allow choose List, click in Source and select A2:A33 on your Players sheet. Tick In-cell dropdown and click OK
  • Paste the Team and Price formulas into C2 and D2 and fill down to row 6. IFERROR keeps an empty pick blank instead of showing an error
  • Paste the points formulas: E2 then fill right to G2 (goals, assists, saves since Round 6), H2 for points, then fill E2:H2 down to row 6. Then D8 for the total, filled right to H8, and the cap check in D9
  • Draft! Use your Players sheet: goals per game, accuracy, saves and price. Pick your 5, check D9 says OK, Ctrl+S and submit. Picks lock when you submit
// Fantasy C2: Team
=IFERROR(INDEX(Players!$B$2:$B$33,MATCH(B2,Players!$A$2:$A$33,0)),"")
// Fantasy D2: Price
=IFERROR(INDEX(Players!$D$2:$D$33,MATCH(B2,Players!$A$2:$A$33,0)),"")
// Fantasy E2: Goals since Round 6 (fill right to G2)
=SUMIFS(Stats!D$2:D$400,Stats!$B$2:$B$400,$B2,Stats!$A$2:$A$400,">=6")
// Fantasy H2: Fantasy points
=E2*4+F2*3+G2
// Fantasy D8: Total (fill right to H8)
=SUM(D2:D6)
// Fantasy D9: Cap check
=IF(D8>50,"OVER CAP","OK")
💡 HintPoints will show 0 today: Round 6 has not been played. They fill in by themselves when you paste the Round 6 stats next lesson.
🔍 Google Image Search"fantasy sports draft salary cap"
// ASK AI"How can I use stats like goals per game and price to pick good value players for a fantasy team?"
📤 Submit via ConnectYour workbook with your 5 fantasy picks (submitting locks them)
// LEVEL UPPick a captain who scores double: type C in column I next to one player and change that row's H formula to =(E2*4+F2*3+G2)*IF(I2="C",2,1), then fill it down to row 6.
08
📊 Chart Room
▾
// MATCHDAY OBJECTIVERound 6 pasted in, a Progress sheet of points after every round, a line chart of the title race and a column chart of goals for each club, both titled.
  • Download Results to Round 6 and Player Stats to Round 6 and paste them into Results and Stats. Check your Ladder, Standings and fantasy points all moved
  • Add a sheet called Progress and paste the Progress Starter into A1: rounds 1 to 7 down column A, the 8 clubs across row 1
  • Paste the formula into B2. It counts points earned up to that round: B$1 locks the club row, $A2 locks the round column. Fill right to I2, then fill B2:I2 down to row 8
  • Line chart: select A1:I8, then Insert, then Recommended Charts, then the All Charts tab, choose Line and click OK. A line chart shows change over time
  • Column chart: on Ladder select A1:A9, hold Ctrl and select F1:F9 (goals for). Insert, then Recommended Charts, then All Charts, choose Column. A column chart compares groups
  • Title both charts: click the chart, click the Chart Elements button (the plus beside the chart), tick Chart Title and type a title that says what the chart shows
// Progress B2: Points after each round (fill right and down)
=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)
💡 HintRound 7 has not been played yet, so its row repeats the Round 6 totals until you paste Round 7 in.
🔍 Google Image Search"line chart points race season"
// ASK AI"When should I use a line chart instead of a column chart? Use sports examples."
// LEVEL UPClick your column chart, then the Chart Elements button, and tick Data Labels so each column shows its number. Then tick Axis Titles and name both axes.
Week 5
09
🖥️ Dashboard
▾
// MATCHDAY OBJECTIVEA Dashboard sheet that shows the leader, top scorer, best keeper and your fantasy points from formulas, with your two charts, tested by pasting in Round 7.
  • A dashboard puts the key facts on one screen. Add a sheet called Dashboard and type these labels in A2 to A6: Leader, Top scorer, Goals, Most saves, My fantasy points
  • Paste the formulas below into B2 to B6. Each one points at a cell or a column that your other sheets already work out
  • Move your charts: click a chart, Ctrl+X, click the Dashboard sheet, click a cell beside your labels, Ctrl+V. Do the same with the second chart
  • Make it readable: big bold labels, a fill colour for the title row, and nothing on the screen that a viewer would need to scroll to find
  • Now the test: download Results to Round 7 and Player Stats to Round 7 and paste them in. Does the dashboard update by itself? Check the leader against Standings and the charts against Progress
  • Write down anything that did not update, find which formula caused it, and fix it. Ctrl+S and submit
// Dashboard B2: Leader
=Standings!B2
// Dashboard B3: Top scorer
=Players!M2
// Dashboard B4: Their goals
=MAX(Players!$F$2:$F$33)
// Dashboard B5: Most saves
=INDEX(Players!$A$2:$A$33,MATCH(MAX(Players!$H$2:$H$33),Players!$H$2:$H$33,0))
// Dashboard B6: My fantasy points
=Fantasy!H8
💡 HintIf a cell shows a number where you expected a name, check you pasted into column B and that the formula points at the right sheet.
🔍 Google Image Search"sports analytics dashboard excel"
// ASK AI"What makes a dashboard easy to read in a few seconds? Give me five tips."
📤 Submit via ConnectYour workbook with the Dashboard sheet, tested with Round 7
10
🏆 Grand Final Night
▾
// MATCHDAY OBJECTIVEThe finals results and stats pasted in live, your dashboard showing the champion and your final fantasy points, a 2-minute analyst talk given, and your workbook and reflection submitted.
  • The top 4 on the ladder play the finals: 1st v 4th and 2nd v 3rd in the semi finals, then the winners meet in the Grand Final. Your teacher releases the finals data live
  • When your teacher says go: download Finals Results, add a sheet called Finals, paste it into A1, type Winner in F1 and paste the Winner formula into F2, then fill down to F4
  • Download Player Stats with Finals and paste it over your Stats sheet. The finals are rounds 8 (semis) and 9 (Grand Final), so your fantasy points jump. Add Champion in A7 of your Dashboard with the formula below in B7
  • Analyst Desk: a 2-minute talk from your dashboard. The champion, the story of the season from your line chart, one player stat that surprised you, and how your fantasy squad went
  • Class awards: Best Dashboard, Sharpest Analyst, Fantasy Champion (most fantasy points) and People's Choice
  • Write your reflection: which formula you are proudest of, one bug you fixed and how, and what you would add to your workbook with more time. Submit both
// Finals F2: Winner
=IF(A2="","",IF(D2>E2,B2,IF(E2>D2,C2,"Draw")))
// Dashboard B7: Champion
=Finals!F4
💡 HintPaste the finals into their own Finals sheet, not into Results. The ladder is for the regular season only, so finals results must not change it.
// ASK AI"Help me plan a 2-minute talk that explains a sports season using a chart and three statistics."
📤 Submit via ConnectYour final workbook (.xlsx) and your reflection
🏆

Grand Final Night Awaits

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.

📊 Live dashboards
🎙️ 2-minute talks
🏆 Four awards

Download Round 1, open it in Excel // Matchday 01

SYSTEM ONLINE // YEAR 9 & 10 DIGITAL TECHNOLOGY JETBALL PRO LEAGUE // 10 MATCHDAYS