IEP data collection spreadsheet starter

A free IEP data collection spreadsheet, delivered as a CSV that opens in Excel, Google Sheets or Numbers. If a spreadsheet is working for you, keep it. This is the column layout that stops one from quietly lying to you, plus the two formulas that do the work.

Where you put it matters more than what is in it. A spreadsheet in a cloud account is in that cloud account. Whether that is an approved place for student information is a question for your district, and it is not answered by leaving the name column blank. Removing a name does not make a record non-identifying.

The columns

Six of these are the ones people leave out, and each omission causes a specific problem later.

Column	Why it is there
date	YYYY-MM-DD, so it sorts correctly and no locale reads 03/04 as a different week.
status	`observed` or `not-observed`. The column that keeps an observed zero apart from a session that never happened.
met	How many opportunities met criterion. Blank when the status is not-observed. Never blank when it is observed.
opportunities	How many were administered. The denominator. Without it the percentage cannot be checked or pooled.
goal_version	Which definition was in force. The column that saves you in June, cross-referenced to your revision log.
context	Setting or activity. Stops you pooling the resource room with the classroom without noticing.
prompt_condition	Which criterion was applied: independent only, or independent and prompted.
collector	Initials or role. When two collectors disagree, this is how you find out.
supersedes	The row number this one corrects. Keeps the original visible without letting it into the total.
note	What happened. The field that turns an odd row into a story you can still tell in four months.

The two formulas

Everything else is a variation on these. Written for a sheet with the header in row 1 and data from row 2 down.

What you want	Formula
Pooled percentage	`=SUM(C2:C200)/SUM(D2:D200)`, formatted as a percentage. Column C is met, D is opportunities.
Mean of session percentages	`=AVERAGEIF(D2:D200,">0",E2:E200)`, where column E is the per-row percentage `=IF(D2>0,C2/D2,"")`.
Sessions counted	`=COUNTIFS(B2:B200,"observed",D2:D200,">0")`
Sessions not observed	`=COUNTIF(B2:B200,"not-observed")`
Total opportunities	`=SUM(D2:D200)`

The IF(D2>0, ...) matters. Without it, a row with no opportunities produces a divide-by-zero, and the usual fix is to wrap it in IFERROR and show a blank. That turns an error into a silent zero-ish gap, which is exactly the distinction this layout exists to preserve. Leave it empty on purpose instead.

Three things a spreadsheet will do to you

It will average the percentage column: Because the column is right there and AVERAGE is one word. That gives the mean of session percentages, which may or may not be what your goal asks for. It is not wrong, it is just frequently unintended. See example 2.
It will treat a blank as a zero: SUM and AVERAGE skip blanks, but plenty of other things do not, and a chart will happily draw a blank row as a point at zero. The status column is your defence.
It will not tell you the definition changed: Nothing in a spreadsheet notices that January’s criterion is not March’s. The goal_version column is the only thing standing between you and a chart that reports a decline that never happened.

When a spreadsheet stops being enough

Usually at the same three points, and not before. It is worth knowing which one you have hit rather than switching tools out of general frustration.

When you are transcribing from paper into it, because that is a second recording step and every one of those loses something.
When more than one person needs to add rows during the school day.
When you have more goals than you can keep tabs for, and the tab names start carrying meaning that nothing enforces.

Until then it is a perfectly good tool, and we would rather say that than pretend otherwise. A free spreadsheet that fully solves your problem is a legitimate outcome.