Teachers shouldn't need a computer science degree to calculate a final grade. Yet here is a dedicated educator, working in a Korean-language Excel interface they cannot change, battling `#DIV/0!` errors and array formulas just to give each test equal weight. The problem is not the teacher. The problem is that traditional spreadsheets were built for accountants, not for classrooms. When a tool designed for row-and-column rigidity meets a human need for flexible, logic-driven grading, the friction is real, and it wastes time that should go to students.
This teacher's goal is straightforward: convert each test score into a percentage, average those percentages equally, and ignore blank cells. Excel can do this, but the path is needlessly obscure. The core issue is that Excel's `AVERAGE` function treats blank cells as zero when combined with division inside the formula, and `SUMPRODUCT` requires array gymnastics to handle partial ranges. The elegant solution, a helper column, is exactly what this teacher is open to, and it's the right call. For each test column, add a row that converts the score to a percentage only if both the score and total are present: `=IF(AND(F6<>"",F$3<>""),F6/F$3,"")`. Then average that helper row with `=AVERAGE(range)` and format as percentage. It adds one row per test, not per student, and it updates automatically. Class average? `=AVERAGE` on the student percentage column. No Korean syntax barrier, no `#DIV/0!`, no weighted distortion.
What stands out here is not the formula, it's the mismatch between the tool and the task. This teacher is not asking for a revolution. They are asking for a spreadsheet that respects their pedagogical intent: equal treatment of assessments, regardless of point values. That is a fair, human-centered request. And it reveals a broader truth: the spreadsheet industry has spent decades optimizing for financial models and data tables, while millions of educators, project managers, and small-business owners bend these tools to purposes they were never designed to serve. The result is workarounds, frustration, and lost productivity.
AI-native spreadsheets change that calculus. Instead of forcing users to memorize arcane function syntax or debug `#VALUE!` errors in a foreign language, a modern tool can interpret intent: *"Average each test as a percentage, ignore blanks, and show the result in column E."* That is not science fiction. It is the logical next step for a technology that should adapt to people, not the other way around. For now, a helper column solves this teacher's problem. But the real lesson is that we can do better, and we should.