The task and original case
Diagnose a PivotTable that counts two entered amounts instead of adding them. The project is a before-and-after import note: preserve the raw text, document its conversion and show how field type changes the aggregation. A displayed currency symbol alone does not make a cell numeric.
| Case element | Value or requirement |
|---|---|
| Inputs | Two entered amounts are the strings $10 and $5 rather than numeric values. |
| Build | A before/after PivotTable and an import-quality note. |
This is an original invented exercise. It is independent practice, with no employer assessment or open-vacancy claim. Download the inputs, attempt your own result, then open the separate answer key.
Data and worked inputs
Build a result another person can follow
Keep the original values $10 and $5 in a raw column. Add a normalized numeric column using a declared single-currency conversion rule: remove the known dollar symbol, trim surrounding spaces and parse the remaining value. Record currency separately and retain failed conversions rather than replacing them with zero.
Build the first summary from the text column and inspect its value-field aggregation. Microsoft's PivotTable guidance explains the choice between Sum and Count. Then build or refresh a summary using the verified numeric column and choose Sum explicitly. Display the source type, aggregation setting, row count and result together so another person can locate the cause of the difference.
Sources[1]
Check the worked result
The text-based count can report two entries. The normalized numeric values are 10 and 5, and their sum is 15. These are different summaries of the same supplied rows: count answers how many nonempty entries exist; sum answers their total under the declared currency rule.
Do not report that the source contained only two dollars because the count displayed 2. Keep a screenshot or settings note showing which field was summarized and how. A correct result must be traceable to the numeric conversion, not a manually typed total.
Make the failure visible
Add the text “unknown” and require it to appear in the conversion-failure register. Add a blank cell and decide whether it is missing rather than a measured zero. A broad replace-all of punctuation can also corrupt decimal or thousands conventions, so keep this exercise's currency and number format explicit rather than claiming universal parsing.
Revise it and ask someone to reproduce it
Ask a peer to add $7, refresh the input range and predict a count of three and numeric sum of 22. If the PivotTable still displays the old total, investigate refresh or source-range coverage separately from numeric parsing. Retain both revisions and explain which step changed the output.
- Raw strings and converted numeric values are both visible.
- Count and Sum settings are identified alongside results.
- Invalid conversions and stale source ranges are checked separately.
Describe the work accurately
After completing your own version, you could adapt this project bullet: “Diagnosed a practice PivotTable count-versus-sum error using retained raw currency text, controlled numeric conversion and explicit aggregation settings.”
Use Projects or Independent practice as the heading. Keep the input files, result and revision together. Describe only your own completed work; the invented numbers are model outputs, not employer results or measured business impact.