Ravindra BagaleCourses & study guides मराठी Track your progress

27. 20 Industry Projects

27.14 Education: Pass Rate, Scores and Attendance

By Last updated:

Example first. Shahrukh coordinates fictional training centres in four cities and wants pass rate, scores and attendance for the batch.

Education project — in 30 seconds

What: A Education tracker: one Table tblStudents, the calculated column Result, KPI cells, a PivotTable by City, a chart and a one-page dashboard.

Why: This is the shape of a real Education report: Pass %, Average Score, Average Attendance are the numbers a manager reads first.

Tiny example: From 6 rows: Pass % = 66.7%, Average Score = 59.2, Average Attendance = 81.8%.

First look at the data (before → after):

Education project: before and after tables, fictional Blinkit Maharashtra data

Expected result — Calculated column

StudentID Result
ST-401 Pass
ST-402 Fail
ST-403 Pass
ST-404 Fail
ST-405 Pass
ST-406 Pass

Expected result — KPI cells (expected results)

KPI Formula Result
Pass % =COUNTIF(tblStudents[Result],"Pass")/ROWS(tblStudents[Result]) 66.7%
Average Score =AVERAGE(tblStudents[Score]) 59.2
Average Attendance =AVERAGE(tblStudents[Attendance]) 81.8%
Before table and formula as text

Before — Table tblStudents (fictional sample rows)

StudentID City Course Attendance Score
ST-401 Pune Excel 92.0% 78
ST-402 Pune Power BI 75.0% 35
ST-403 Nashik Excel 88.0% 64
ST-404 Nagpur Excel 60.0% 38
ST-405 Kolhapur Power BI 95.0% 88
ST-406 Nashik Power BI 81.0% 52

Formula / click path

Result: =IF([@Score]>=40,"Pass","Fail")
Pass %: =COUNTIF(tblStudents[Result],"Pass")/ROWS(tblStudents[Result])
Average Score: =AVERAGE(tblStudents[Score])
Average Attendance: =AVERAGE(tblStudents[Attendance])

Do it step by step:

  1. Type the data and press Ctrl+T; name the Table tblStudents in Table Design › Properties › Table Name.
  2. Add the column Result with =IF([@Score]>=40,"Pass","Fail"); the Table fills it down.
  3. On a KPI sheet type =COUNTIF(tblStudents[Result],"Pass")/ROWS(tblStudents[Result]) and the other KPI formulas from the table below.
  4. Click Insert › PivotTable › From Table/Range, put City in Rows and Score in Values (Average).
  5. Click PivotTable Analyze › PivotChart › Clustered Column, then PivotTable Analyze › Insert Slicer › Course.
  6. Move the KPI cells, the chart and the slicer onto a sheet named Dashboard.

The brief. Build a results sheet: Pass/Fail per student (40 or more passes), pass %, average score, average attendance, average score by city (Value Field Settings › Average), a chart and a Course slicer.

Calculated column formulas:

Column Formula
Result =IF([@Score]>=40,"Pass","Fail")

KPI cells (check your numbers):

KPI Formula Result
Pass % =COUNTIF(tblStudents[Result],"Pass")/ROWS(tblStudents[Result]) 66.7%
Average Score =AVERAGE(tblStudents[Score]) 59.2
Average Attendance =AVERAGE(tblStudents[Attendance]) 81.8%

PivotTable (expected):

City Average of Score
Kolhapur 88.0
Nagpur 38.0
Nashik 58.0
Pune 56.5
Grand Total 59.2

Chart and dashboard (one under the other):

  1. Click inside the PivotTable, then PivotTable Analyze › PivotChart › Clustered Column › OK.
  2. Add a chart title and data labels with Chart Design › Add Chart Element.
  3. Click PivotTable Analyze › Insert Slicer, tick Course and click OK.
  4. Insert a new sheet named Dashboard. In it, link a card cell to each KPI (for example =KPI!B2) and format it large and bold.
  5. Cut and paste the chart and slicer onto the Dashboard sheet.
  6. On the View tab, untick Gridlines in the Show group. Select the dashboard area and click Page Layout › Print Area › Set Print Area.

Template and guide: download the practice template Education_Template.xlsx and read the step-by-step template guide. The template's sample rows may differ from the rows above; the method is the same.

Related ready-made dashboard: the Education dashboard guide and its workbook Education_Dashboard.xlsx show a finished version of this kind of report.

Practice task (try it before you open the answer):

  1. Which city has the highest average of Score?
  2. Filter the Table on City = Kolhapur (Data › Filter): what is Pass % for Kolhapur alone?
  3. Add the Clustered Column PivotChart and the Course slicer to your Dashboard sheet.
Answer

Kolhapur (88.0). For Kolhapur only (1 row): Pass % = 100.0%. The chart shows 4 bars, one per city.

Ravindra Bagale's Tip

Show Average Score next to the rupee total. A percentage or ratio without the size behind it can make a small city look like a big problem.

Loading practice tasks...

  1. Pass or fail (Easy): "Pass" if B2 is at least 40, else "Fail". (sheet "Scores")
  2. Total (Easy): B2 plus C2. (sheet "Scores")
  3. Passed score 1 (Easy): How many in B2:B11 are 40 or more? (sheet "Scores")
  4. Days taken (Easy): C2 minus B2. (sheet "Dates")
  5. Start year (Easy): Year of B3. (sheet "Dates")
  6. Due month (Easy): Month number of C3. (sheet "Dates")
  7. Earliest start (Easy): Earliest date in B2:B9 (as a date number). (sheet "Dates")
  8. Passed both (Medium): TRUE if B3 and C3 are both at least 40. (sheet "Scores")
  9. Passed either (Medium): TRUE if B4 or C4 is at least 75. (sheet "Scores")
  10. Grade (Medium): Grade for the average of B2:C2: "A" 75+, "B" 60+, "C" 40+, else "F". (sheet "Scores")
  11. Working days (Medium): Working days from B2 to C2. (sheet "Dates")
  12. Weekday of due (Medium): Weekday number of C4 (Sunday = 1). (sheet "Dates")

See all practice tasks in the Task Hub →