IF, LEN, ISBLANK for validation
The cleanest move is to not delete placeholders — you might want to come back to them later — but to flag them with a Boolean column. Then every percentage in the report references the flag column as its denominator.
Building a Valid_Phone column
In your roster, add a new column titled Valid_Phone. In the first data row, write:
=IF(LEN(C2) < 10, FALSE, IF(C2 = "(000) -", FALSE, IF(C2 = "N/A", FALSE, TRUE)))
Read it inside-out:
- If
LEN(C2)is less than 10, the cell is too short to be a phone. Return FALSE. - If the cell equals
(000) -, return FALSE. - If the cell equals
N/A, return FALSE. - Otherwise, return TRUE.
Drag down. Every row now has a TRUE/FALSE flag.
The cleaner version using OR
Nesting IFs gets ugly fast. The same logic with OR is much easier to read:
=IF(OR(LEN(C2)<10, C2="(000) -", C2="N/A", C2="TBD"), FALSE, TRUE)
If any of the conditions inside OR are true, the cell fails validation. Otherwise it passes.
Wrap risky formulas in IFERROR
Some formulas crash on bad data — a VLOOKUP that misses, a date function on a string, a divide by zero. The fix is to wrap them in IFERROR:
=IFERROR(your_formula_here, "")
If the inner formula errors, the cell shows an empty string instead of #N/A. The error does not propagate to your report.
Action: Mark this page complete when you have finished the activity above.