Data and spreadsheets
Describe a spreadsheet formula problem clearly
Name the input columns, say what the output cell should show, and include a few sample rows with the result you expect for each. Say which spreadsheet program you use, since function names and behavior differ between programs. The sample rows let you check any proposed formula before you rely on it.
A text to start with
I use [spreadsheet program]. My data has these columns: [column letters, names and types]. I want [output description] in [target column or cell]. Here are sample rows with the result I expect: [rows and expected results]. Include awkward rows such as [blank cells, ties, text in number columns]. Propose a formula and explain how it handles each sample row.
Example request. Change the details to fit your situation.Formula brief
Spreadsheet program and version: [ ] Sheet and range holding the data: [ ] Input column: [ ] What it contains: [ ] Type (number, text, date): [ ] May it be blank? [ ] Desired output and where it goes: [ ] Rule in plain words: [ ] Sample row 1 values: [ ] Expected result: [ ] Sample row 2 values: [ ] Expected result: [ ] Sample row with a blank: [ ] Expected result: [ ] Sample row on a boundary value: [ ] Expected result: [ ] Sample row with a duplicate key: [ ] Expected result: [ ] Repeat the item block as needed.
Where formulas fail
Formulas usually fail on rows the sample left out. Check blank cells, since many functions treat a blank as zero or as an empty string. Check duplicate keys when the formula looks up a value, because a lookup may return only the first match. Check boundary values, such as a score exactly on a cutoff, to see whether the comparison includes it. Look for numbers or dates stored as text. Confirm the formula still works when copied down, including in the last row.
Ask another agent to check the result
Compare the formula with my supplied rows and expected results. Propose clearly labeled hypothetical cases for blanks, duplicate keys and boundary values that the samples omit. Explain the expected result under my rule for each case. Mark behavior you have not actually tested and identify any ambiguity in the rule.
Test before filling down
Enter the formula in one row and compare its result with your expected value. Repeat for each sample row, then for two or three rows you did not mention. Fix any mismatch before filling the formula down the column. Keep a copy of the original file, and note the formula and its purpose in a cell comment or on a separate sheet.