You have inherited a monthly sales report. The person who built it has left.
Your manager wants "the same thing, updated" by Thursday, and mentions in
passing that the numbers "have looked a bit off lately".
Nothing here is exotic. Every one of these appears in real files constantly, and
the report has been circulating for three months with people making decisions
from it.
The difficulty is not fixing any single problem. It is noticing them at all,
before you build on top of them — and then deciding which ones actually change
the answer versus which are merely ugly.
At least four distinct problems, of different kinds.
For each: how you would detect it in a file you had never seen. A method,
not an observation. "I would check whether the SUM range matches the data
range" generalises. "Row 3847 is wrong" does not.
Which problems change the total and which are cosmetic.
What you would tell your manager, in under 100 words, including anything
you cannot resolve without the person who left.
Before examining any row, verify the total independently. Select the amount
column and look at the status bar sum, or write a fresh SUM over the full range.
If your number differs from the one in the file, you have found something before
spending an hour reading rows. Always establish whether the headline number is
even correct first.
Rank the problems by whether they announce themselves.
#N/A is loud — you can see it. IFERROR(...,0) turns twelve failed lookups
into twelve zeros that look exactly like real data. Nobody sees a problem, and
the total is quietly wrong.
Inconsistent capitalisation in Region is loud once you group by it — you get
four "North" categories. Hidden rows are silent.
The silent ones are the ones that have been misleading people for three months.
A number stored as text does not get added. It also usually aligns left instead
of right, which is the visual tell.
Anything containing a currency symbol, a comma typed manually, or a leading
space will often be text. COUNT versus COUNTA on the same column will differ
if some cells are text — that difference is your detector.
A strong assessment identifies problems across categories:
Structural — the SUM range not matching the data extent; a TOTAL row inside
the data range being double-counted; hidden rows silently excluded or included
depending on the function used.
Type — dates stored as three different types, so any date filtering or
grouping is unreliable; amounts stored as text, so they are excluded from
totals.
Consistency — North / north / NORTH / Nrth producing four regions
where there is one; four different words for the same status; names in two
formats, so grouping by salesperson splits people in half.
Concealment — IFERROR converting failed lookups into zeros. This is the
single most important find, because it is deliberate-looking, invisible, and it
changes the total.
Contamination — rows marked TEST sitting in production data.
The detection methods that generalise:
Verify the headline total independently before reading anything
Compare COUNT and COUNTA on numeric columns to find text-typed numbers
Build a quick pivot or unique list on every text column to expose variant
spellings immediately
Check whether any SUM range matches the actual data extent
Search formulas for IFERROR and ask what it is hiding
Unhide everything before trusting any total
The strongest answers also separate "changes the number" from "makes the file
unpleasant", and say clearly what could not be resolved — for example, whether
the 40 blank regions should be excluded or attributed, which is a business
question nobody left an answer to.
If your manager had said "just update it, it's fine", would you have checked?
The honest answer for most people is no. Inherited files carry an assumption of
correctness that they have not earned, and three months of decisions were made
on this one.
Your attempt
Constraints
·You must find at least four distinct problems
·For each, state how you would detect it in a file you had never seen
·You cannot ask the person who made it — they have left