Search code examples
google-sheetsconditional-formattingfinancezeronegative-zero

When working with financial data in Google Sheets, why are some of my "0.00" values reporting as negative or positive?

I'm currently working with financial data and there's a lot of manual data entry. I'm using a dedicated SUM column combined with conditional formatting to double check my work.

Using this number formatting: #,##0.00;(#,##0.00)

I'm working with payroll information tracking: Gross pay, Pre-Tax Deductions, Employee Taxes, Post-Tax Deductions, Net Pay

To double check my work, in a separate column I'm adding up all of the columns listed above except for Net Pay. Then using conditional formatting to highlight any entries where the Net Pay and SUM do not match.

There are certain entries, such as bonuses or PTO compensation, that zero out because the payroll system handles them in a different way as compared to the normal paystub. The resulting SUM ends up showing a negative (0.00) value and my conditional formatting tags this as a mismatch.

For example,

Gross Pay: 1000.00
Pre-Tax: 0.00
Employee Tax: (300.00)
Post-Tax: (700.00)
Net Pay: 0.00

SUM Check: (0.00) X . . . . . . . . What is happening here?

Extra Notes:

  • There are also entries where a positive 0.00 value is marked as incorrect.
  • There are also entries where a 0.00 value is seen as correct and no conditional formatting is applied
  • Using MIN and MAX I can force these values to be a true 0.00, but that feels like I'm solving the problem with a hammer.
  • I have extended out the number formatting to see if there are any values past 2 decimal places, but as far as I can see it's truly a 0
Solution

  • It is negative because it is not zero. Just do round(your number, 2) and you will see.