Diagnosing the Spreadsheet Unit: When the Prerequisite Is the Lesson
Ms. Okafor teaches a 7th grade Technology Applications course with 28 students on 1:1 Chromebooks (4 GB RAM, no local admin rights). Her next unit was going to cover nested IF statements and VLOOKUP in spreadsheets. Before starting, she gave a six-item data-literacy check. Results: 12 of 28 students could not match a column of values to an appropriate chart type; 9 sorted a table without noticing that one column was stored as text; and 4 wrote a formula that summed a merged-cell range and returned 0. Three students finished the check in under five minutes and correctly explained why the text-formatted column would not average. The campus instructional coach has 40 minutes per week available to co-plan or co-teach.
1. Ms. Okafor wants to keep the VLOOKUP unit exactly as written and simply add one review day in front of it. Evaluate that plan.
Read the worked answer
A single review day addresses the wrong problem, because the check reveals three different failure modes. Four students hit a structure problem (merged cells breaking a range), nine hit a data-typing/diagnosis problem (text stored in a numeric column), and twelve hit a representation problem (choosing a chart for a question). Nested IF and VLOOKUP sit on top of all three, so pushing forward after one review day means students will again produce 0s and #N/A errors with no idea why. A better sequence: two days on diagnosing data (ISTEXT/ISNUMBER, observing sort behavior, unmerging and why to avoid merged cells), one day on selecting a representation for a stated question, then into VLOOKUP taught as a debugging skill in which students explain the error before they fix it. The check itself becomes the pre-assessment evidence for the revised sequence, and the coach's 40 minutes can be aimed at the nine students with the typing gap.
2. During practice, a student shows her a spreadsheet where =SUM(D2:D20) returns 0 even though the column looks numeric. What should she do in the moment, and why?
Read the worked answer
Resist typing the fix. Ask three diagnostic questions in order: what type of data does the cell actually hold, what does SUM do with text, and which tool would tell you the type? Have the student select D2 and read the alignment and status bar, then test with =ISTEXT(D2); the giveaway is usually left alignment plus a warning triangle, or numbers imported with an apostrophe or a trailing space. Then let the student choose the repair (Text to Columns, VALUE(), TRIM/CLEAN for stray spaces) and predict the new total before recalculating. The routine is the point: observe the symptom, isolate the cell, test the type, repair, verify. The same routine handles #N/A next week. She should also say out loud that the formula was correct and the data was wrong, because students routinely assume a 0 means the formula is broken and start rewriting working logic.
3. How should she use the three early finishers and the coach's 40 weekly minutes so that neither turns into busy work?
Read the worked answer
Give the three early finishers a bounded, visible job rather than making them tutors. Ask them to build three 'broken' workbooks for peers to debug: one merged-cell trap, one text-column trap, one chart-choice trap, with a hidden answer key. Building the traps forces them to articulate the concept, which is exactly where their understanding is thinner than their scores suggest, and it yields reusable materials. Pair them specifically with the nine students who missed the typing issue, not with the twelve who cannot choose a chart; chart selection needs her direct instruction. Spend the coach's time on co-planning the two diagnostic days rather than push-in support, because the leverage is in the sequence, not in a second adult in the room. A reasonable split is 20 minutes co-planning and 20 minutes with the coach modeling a think-aloud for the #N/A case, which is where the unit will bottleneck.