IF formulas works on & off - returning 0 or (0)

[SRE]

I have a simple IF formula - if the answer = 0 then return BALANCED, if not it should return the actual #. Here is the problem, some of the problems return the word BALANCED as it should, some are returing 0 or (0)?! I'm copying & pasting my formula so no room for error there?! I'll show you exactly the situation:

A1 - enter #
A2 - enter #
A3 - (formula) +A1-A2
A4 - enter #
A5 - (formula) +A3-A4
A6 - enter #
A7 - (formula) =IF((+A5-A6)=0,"BALANCED",(+A5-A6))

Again, sometimes it returns BALANCED but others it will return a 0 or even a (0).

Please help

[davegugg]

Can you post a sample workbook showing your problem?

[SRE]

A1 10
A2 3
A3 7 Formula is +A1-A2
A4 5
A5 2 Formula is +A3-A4
A6 2
A7 BALANCED Formula is =IF((+A5-A6)=0,"BALANCED",(+A5-A6))


Thanks davegugg

[Miraun]

SRE;

Without seeing the full sample workbook to see the problems you're describing, it's hard to determine the cause of the 0 value rather than "Balanced"...

One thing that comes to mind is that the format on the individual cells is to round the values to whole numbers, while the numbers contained within the cell contain decimals. That would result in a .4 value difference between the two balances showing a 0 value, but still not being "Balanced".

[SRE]

Miraun,

The format was my thought as well, however, I don't know how to change it from not rounding (if that is the case)? I went in and formated all as a number, with a comma and decimal place 2.

Any suggestions?

[davegugg]

How about posting a sample workbook?
Post 3 does nothing to help us understand you problem because it doesn't show the issue you describe in your original post.

[SRE]

Sorry I just joined today so I'm not sure how to go about posting a sample workbook?

[Miraun]

You can always change the success criteria to being a less than 1 difference, if there are unknown decimals.

=IF(abs((+A5-A6)<1),"BALANCED",(+A5-A6))

However, that being said, if it's rounding, it may be possible to have over a 1 difference, even if the numbers appear to result in 0. I wouldn't be able to provide anymore insight to the formatting or rounding without seeing an example where it was showing 0, and not balanced.

[SRE]

Where do I change the settings for rounding?

Thanks

[davegugg]

Please see the forum rules, which describe how to attach a sample workbook.

[SRE]

Here is my document, as you will see the 1st situation came out correct - BALANCED - this others have either a 0 or a (0)??

Thanks

[davegugg]

This has to do with how Excel stores numbers. If you show more decimals, you will see the actual number in D14 is (0.0000000000146...) because the actual number in D10 is not a whole number either. To solve this, you could use the ROUND function and round it to two decimal places.