Forum Discussion

CubanPete's avatar
CubanPete
Copper Contributor
Feb 12, 2025

Cell Value Comparison

Hi,

Please can someone help me as I am tearing my hair out right now and Copilot AI is obsessed with header rows and no help at all.

I have a spreadsheet with a load of numbers on it and various column formatting (general/dates/currency).

I have a formula in a column that compares the difference of 2 other columns and returns the value. If either of the cells are empty then no value is displayed.

For some reason the formula hates the value £121.80 and returns a negative value and I have no idea why.

 

Here is an example

My formula is =IF(OR(AC4="",X4=""),"",AC4-X4) on the selected cell. But you can see there are 5 negative values and are all £121.80 in AC.

Please, any advice would be greatly appreciated!

Thanks,

H

Formulas and Functions
  • CubanPete's avatar
    CubanPete
    Copper Contributor
    Feb 17, 2025

    Thanks, ive checked everything. They are all set to 2 decimal places. It is only when the number is £121.80 that I get a minus result. 

    I've tried copying the data into a new workbook and still have same problem. 

  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Feb 12, 2025

    Both AC4 and X4 are not empty, thus we have AC4-X4. Most probably values in these cells are not rounded, e.g. in X4 we have 128.8002 and in AC4 128.8001. Difference is -0.0001. Applying currency format which shows 2 decimals we see -0.00.

    If you'd like to have exact zero it shall be like ROUND(AC4,2) - ROUND(X4,2).

Resources