220-1102 Question 303
Single answerSpreadsheetsA small business manager says a monthly sales spreadsheet is giving incorrect totals. In the Total column, the formula in cell D2 is =B2+C2, and the employee dragged that formula down through D50. Later, a new column for regional tax was inserted between columns B and C. Some totals now appear too low, and the manager wants the quickest fix without manually editing dozens of cells. What should the technician recommend?
- A
Replace the formulas in the Total column with =SUM(B:C) in each row
- B
Update the formula in the first affected row to include the correct cells, then fill or copy it down the column
- C
Format the Total column as Currency so the spreadsheet recalculates correctly
- D
Sort the sheet by the Total column so the formulas reference the right rows
Show answer and explanation
Correct answer: B
Explanation
The best recommendation is to correct the formula in the first affected row and then fill it down. This is a common spreadsheet support task: when worksheet structure changes, formulas may need to be reviewed to ensure they still include the intended cells. Best practice is to verify the formula references after inserting columns or moving data, then use AutoFill to efficiently apply the corrected formula to similar rows. In Excel and Google Sheets, formulas are designed to recalculate automatically, but they can only calculate correctly if the cell references are correct. Formatting and sorting do not fix broken logic in formulas. Spreadsheet documentation from Microsoft and Google both emphasizes checking formula references and using fill operations to copy corrected formulas consistently across a range.
- A. Incorrect.
This is incorrect because =SUM(B:C) sums entire columns B and C, not just the cells in the current row. In a row-based sales total, the formula should reference the intended cells for that row, such as =B2+C2+D2 or an equivalent range depending on the new layout. Using whole-column references here would produce incorrect results and likely much larger totals than expected.
- B. Correct.
This is correct. After a new column is inserted, the technician should verify which cells now contain the values that belong in the row total, correct the formula in the first affected row, and then use AutoFill or copy/paste to apply it to the remaining rows. This is the fastest practical method and matches normal spreadsheet troubleshooting practice in Microsoft Excel, Google Sheets, and similar applications.
- C. Incorrect.
This is incorrect because changing number formatting only affects how values are displayed, not which cells a formula calculates. A common misconception is that formatting issues cause calculation errors, but in this scenario the underlying cell references in the formula are the problem.
- D. Incorrect.
This is incorrect because sorting changes the order of data but does not repair an incorrect formula design. Spreadsheet programs generally preserve row-relative formulas during sorting, so sorting would not make missing values suddenly become part of the calculation. This option reflects a misunderstanding between data organization and formula logic.