“N/A” and dashes in a column that should be numbers
A numeric column contains “N/A”, “-”, “n.a.”, or “(blank)” alongside real figures.
Why it happens
- These are all a human writing “nothing here” in a cell that has no way to say it.
- They do not all mean the same thing, which is what makes replacing them with zero dangerous.
- There are several dash characters and they are not interchangeable to a computer.
What to do now
- Decide what absent should mean for this column before touching anything, and write it down.
- Never blanket-replace placeholders with zero in a column that is summed or averaged.
- Search for each dash character separately, or match a character class covering all of them.
Next time
The common placeholders are recognised as blank rather than as text: an empty cell, a hyphen, an en dash, an em dash, a double hyphen, “n/a”, “n.a.”, “(blank)”, and “(empty)” are all read as nothing there.
Start from the inventory count template.
Related problems
My CSV columns are shifted or split in the wrong placesMost rows look right, but some have values in the wrong columns: a postcode in the country column, a phone number where the email should be.My supplier's prices are a thousand times wrongPrices from a European supplier are out by a factor of about a thousand, or occasionally by a factor of a hundred.The plus sign disappeared from my phone numbersInternational phone numbers have lost their leading plus.My CSV shows é where it should show éNames and addresses are full of sequences like é, ü, or ’ where accented letters, umlauts, and apostrophes should be.