22.15 Education

By Ravindra Bagale Last updated: 6 October 2026

Institute and coaching-centre interviews ask attendance, pass rate, and fee collection. Enrolment is the vanity metric: a full bench that does not attend, does not pass, and does not pay. These centres are fictional. Do not present the pass rate as a board result.

KPI	What the number means	Who uses it	Action it drives
Attendance %	Student-days present divided by student-days possible.	Centre head	Low attendance is a call to the student, not a new batch.
Pass %	Students passed divided by students who appeared.	Faculty lead	A low pass with high attendance is teaching or the paper, not "they did not come".
Fee collection %	Fees collected divided by fees due.	Centre admin	A low rate is a follow-up list. Do not add seats to cover unpaid fees.

Fictional sample rows. One week of attendance, and one test. Possible student-days are already in the table (students × working days). Type these rows into a new sheet from cell A1, press Ctrl + T, and name the table tblCentres (Table Design › Table Name).

Centre	City	Students	Present days	Possible days	Appeared	Passed	Fee due	Collected
Pune	Pune	40	180	200	38	30	₹80,000	₹72,000
Nashik	Nashik	30	120	150	28	26	₹60,000	₹60,000
Kolhapur	Kolhapur	25	80	125	20	12	₹50,000	₹30,000
Nagpur	Nagpur	35	140	175	30	24	₹70,000	₹56,000
Solapur	Solapur	20	90	100	18	16	₹40,000	₹36,000
Sambhaji Nagar	Sambhaji Nagar	22	70	110	16	10	₹44,000	₹22,000

Excel formulas for the whole file (type them in empty cells below or beside the table):

KPI	Excel formula	Result	Number format
Attendance %	`=SUM(tblCentres[Present days])/SUM(tblCentres[Possible days])`	79.1%	`0.0%`
Pass %	`=SUM(tblCentres[Passed])/SUM(tblCentres[Appeared])`	78.7%	`0.0%`
Fee collection %	`=SUM(tblCentres[Collected])/SUM(tblCentres[Fee due])`	80.2%	`0.0%`

Calculated columns (one result per row). Click the first empty column of the table, type the header, then the formula. The Table fills every row by itself.

New column	Formula in the first row
Attendance %	`=[@[Present days]]/[@[Possible days]]`
Pass %	`=[@Passed]/[@Appeared]`
Fee collection %	`=[@Collected]/[@[Fee due]]`

Expected result per row:

Centre	Attendance %	Pass %	Fee collection %
Pune	90.0%	78.9%	90.0%
Nashik	80.0%	92.9%	100.0%
Kolhapur	64.0%	60.0%	60.0%
Nagpur	80.0%	80.0%	80.0%
Solapur	90.0%	88.9%	90.0%
Sambhaji Nagar	63.6%	62.5%	50.0%

One city with SUMIFS. Attendance % for Pune only:

=SUMIFS(tblCentres[Present days],tblCentres[City],"Pune")/SUMIFS(tblCentres[Possible days],tblCentres[City],"Pune")

Result for Pune: 90.0%. Change "Pune" to a cell reference (for example $K$1) and add a drop-down, and the same formula answers for any city.

	Good KPI	Vanity metric
From the same rows	Nashik pass 92.9% and fees 100%	Pune 40 students
Why	Nashik is smaller and the test result is the best in the file.	Pune wins a headcount contest. Eight of 38 who appeared did not pass, which enrolment does not say.

What the manager does. The Nashik centre head writes down what the 26 who passed actually practised, and shares that with Pune. The Pune head does not open a second batch because 40 is a round number. Pune attendance is already 90%. The gap is the 8 who appeared and did not pass. Kolhapur is the urgent row: attendance 80 ÷ 125 = 64.0%, pass 12 ÷ 20 = 60.0%, fees 30,000 ÷ 50,000 = 60.0%. Call the absent list before printing a new admission ad.

Practice workbook and guide: Education dashboard workbook (Excel) · How to build the Education dashboard in Excel. The workbook data is also fictional.

Ravindra Bagale's Tip

Pass % uses students who appeared, not students enrolled. A student who did not sit the exam is neither pass nor fail.