Formula equaling 0 but not displaying string

[Margate]

Hello everyone,

In my spreadsheet I have an if formula that should be displaying "Balancing" is the calculation is equal to 0. Problem is the sums is equal to zero but the return is not displaying "Balancing" it is displaying 0.00.
I have tried numerous solutions but to no avail? Rather than explain this formula any further I think it is easier to attach the spreadsheet.

Thank you for any help : )

[Glenn Kennedy]

It was easy. Excel can't count.

=IF(ROUND(SUMIF(Accounts!$G$3:$G$2649,"Pension",Accounts!$I$3:$I$2649)-SUM(F19+F37+F55+F71+F89+F105+F121+F139+F155+F171+F187),2)=0,"Balancing",ROUND((SUMIF(Accounts!$G$3:$G$2649,"Pension",Accounts!$I$3:$I$2649)-SUM(F19+F37+F55+F71+F89+F105+F121+F139+F155+F171+F187)),2))

See link:

https://support.microsoft.com/en-gb/...sults-in-excel

[FDibbins]

The problem is a known "problem" with excel based how excel calcs using the floating point method. see here for a description
https://en.wikipedia.org/wiki/Floating-point_arithmetic

A quick way around this would be to use ROUNDUP, either in the formulas above, or
=IF(ROUNDUP(L5,2)-ROUNDUP(L6,2)=0,"Balancing",L5-L6)

Also, you could use this in L6
=SUMIF(A:A,"Total",F:F)

[Margate]

Brilliant, thank you very much. I tried trunc but that did not help. I expanded the numbers to see if there ware numbers further down the decimal but nope! Been pulling my hair out over this one...

[Margate]

I will note this down and read the Wiki. I need to learn these things as it will happen to me again at some point : )

[FDibbins]

Happy to help and thanks for the feedback

[FDibbins]

On a side note, not sure if it would have made any difference, but I used roundUP rather than just round, to make sure they both got rounded the same way