Negative numbers in several notations
Negative amounts are written with a leading minus, a trailing minus, parentheses, or a credit marker, all in one column.
What it looks like
Before
| balance | what is actually there |
|---|---|
| -350.00 | leading hyphen |
| (350.00) | accounting parentheses |
| 350.00- | trailing minus |
| −350.00 | minus sign character, not a hyphen |
| 350.00 CR | credit marker |
After
| balance | what is actually there |
|---|---|
| -350.00 | unchanged |
| -350.00 | converted |
| -350.00 | converted |
| -350.00 | converted |
| needs a decision | sign depends on the account |
How it happens
- Accounting systems print negatives in parentheses or with a trailing minus, and the printed form was exported.
- Text copied from documents carries a typographic minus sign that is a different character from the hyphen on a keyboard.
- Some ledgers mark direction with CR and DR instead of a sign.
What it breaks
- Values in an unrecognized notation are read as text and left out of totals.
- Stripping the punctuation to make the column numeric turns negatives into positives.
- Refunds, reversals, and credits are counted as income, or not counted at all.
How to spot it
- Search the column for parentheses, trailing minus signs, and the letters CR and DR.
- Total the column and compare with a figure you trust. A total that is too high suggests negatives were dropped or flipped.
- Check whether a value that looks negative is treated as a number. If it sorts with the text, the minus may be the wrong character.
How to fix it
- Convert each notation to a plain number with a leading minus, before removing any other punctuation.
- Replace typographic minus signs with the ordinary hyphen that software reads as a sign.
- Confirm what CR and DR mean for this account with whoever owns the ledger. The convention is not the same for every account type.
- Numbers stored as textA column of quantities contains symbols, separators, or words that stop software reading it as numbers.
- Smart quotes and look-alike charactersA character in the cell looks like an ordinary quote, hyphen, or letter but is a different character underneath.
- Mixed currenciesAmounts in one column are in different currencies, marked by a symbol, a code, or nothing at all.
- Case example: Finance teamsA vendor list that paid the same invoice twice
TermsParsing, Type coercion, Locale, Standardization