Stop Guessing Spreadsheet Formulas: A Teacher's AI Cheatsheet

Every teacher I know has lost a Sunday afternoon to a gradebook formula. VLOOKUP won't find the student, the conditional format doesn't trigger, the average is off by one because of a hidden row. This is the exact task AI is best at — narrow, deterministic, well-documented — and it's the one most teachers still do by hand.

Here's a compact cheatsheet: eight formulas AI writes reliably, the exact way to describe what you want, and the common trap in each. Works in Google Sheets and Excel unless noted.

How to describe a formula to AI

A good formula prompt has four parts. Miss any and the AI guesses:

1. The tool — "In Google Sheets" or "In Excel."
2. The data shape — "Column A has student names, column B has scores 0–100, row 1 is a header."
3. The exact goal — "I want a formula in column C that returns 'pass' if score >= 70, 'retake' if between 50 and 69, 'intervene' below 50."
4. The output type — text, number, date, conditional format, script.

With those four pieces, most formulas come back correct on the first try.

1. Weighted final grade

Ask: "In Google Sheets, column A is student name, B is homework average, C is test average, D is project average. I want column E to return a weighted final: homework 30%, tests 50%, projects 20%. If any of B/C/D is blank, return 'incomplete'."

Trap: the blank-handling. Without asking, the AI will silently treat blank as zero, which destroys grades for excused absences.

2. Lookup a student across sheets

Ask: "In Excel, sheet 'Roster' has student name in column A and student ID in column B. Sheet 'Grades' has student ID in column A. I want a formula in 'Grades'!B that shows the student name from Roster."

Trap: VLOOKUP fails if the ID column isn't on the left in the lookup range. Ask for INDEX/MATCH or XLOOKUP explicitly — it's more forgiving.

3. Count how many students met a standard

Ask: "In Google Sheets, columns B through H are seven standards, marked 'M' (met), 'P' (partial), or blank. In column I I want a count of how many 'M' values are in that row."

Trap: COUNTIF with a wildcard is a common wrong answer. The right function is COUNTIF with exact match, or COUNTA if you want partials to count too.

4. Highlight late assignments in red

Ask: "In Google Sheets, column D is a due date, column E is a submitted date (or blank). Set up conditional formatting so E turns red if E is later than D, or if E is blank and D is in the past."

Trap: the "blank and past" case. AI often skips it unless you name it.

5. Class average excluding lowest score

Ask: "In Google Sheets, row 2 columns B through H have seven quiz scores for one student. I want a formula in column I that averages them but drops the single lowest score."

Trap: ties. If two scores tie for lowest, which one drops? Specify: "drop only one, doesn't matter which."

6. Auto-generate a seating chart

Ask: "In Google Sheets, column A has 28 student names. I want a script (Apps Script) that randomly places them into a 4x7 grid in cells C1:I4, with a button to re-shuffle."

Trap: this needs a script, not a formula. Ask explicitly for Apps Script (Google) or a Macro (Excel). Also ask for the button — the AI won't add it unless prompted.

7. Attendance streak counter

Ask: "In Google Sheets, row 3 has a student's attendance across 40 days in columns B–AO, marked 'P', 'A', or 'T'. I want a formula in column AP that returns the length of the current consecutive 'P' streak ending on the last non-blank column."

Trap: blank days at the end of the row (future days that haven't happened yet). Say "ignore blank columns at the end" or the streak will read as 0.

8. Convert percentage to letter grade with pluses/minuses

Ask: "In Google Sheets, column D has a numeric score 0–100. Column E should return a letter grade using this scale: 93+ A, 90–92 A-, 87–89 B+, 83–86 B, 80–82 B-, 77–79 C+, 73–76 C, 70–72 C-, 67–69 D+, 63–66 D, 60–62 D-, below 60 F."

Trap: nested IFs get unreadable fast. Ask for IFS (Google) or the newer IFS in Excel — cleaner and easier to edit later.

A workflow that saves the Sunday

Whenever you catch yourself Googling for a formula, stop and paste the same query into an AI chatbot with the four-part prompt above. Read the answer, drop it into a test cell, poke it with weird inputs (blank, negative, text where number expected). If it survives those, use it. If not, tell the AI which input broke it — it fixes it on the second turn about 80% of the time.

This one workflow is worth as much as every other AI use in a teacher's week combined. It's not glamorous. It gives you your Sunday back.