ABS returning a 0 from a negative 2

@ Jose,

If the cell contains a function, then it's probably not outputting as text. I say probably because it could have a TEXT() wrapper or concatenation or something -- but that's relatively unlikely.

Even if it was stored text though, ABS will force a number stored as a text string to evaluate as a number.

@ msbeaudry,
What kind of formatting is applied to the cell?
Are there other functions wrapping, or have you simplified what's going on?

As dosydos said, it's very difficult to diagnose strange behavior based only on a description, without seeing the worksheet.

This is the set of formulas relating to the error. I can not attach the full spreadsheet as it is 27MB.
I tried formatting the cells as numbers and as general.


=B303+B278+B247+B206+B172+B141+B110+B78+B29
=AVERAGE(B285,B264,B262,B253,B222,B224,B213,B190,B188,B179,B158,B156,B147,B127,B125,B116,B96,B94,B85,B64,B62,B53,B11)
=B307-B308

=ABS(B312)

=B27+B45
0
=ABS(SUM(B316-B320))

=SUM(B317:B319)
=B320-B316
=B318*40
=B319*40
=B322+B323
=IF(B321<0,"short","")

=B76
2


=SUM(B328:B330)
=B331-B327
=B329*40
=B330*40
=B333+B334


=B316+B327
=B320+B331
=B339-B338
=B339*40


=B170
3.5

=SUMIF(B352,)

It can be difficult to diagnose this sort of thing from a list of functions. I would suggest that you spend some time with your formulas to build a good sample spreadsheet that illustrates your problem.

I tried pasting those formulas into a blank spreadsheet, but I could not replicate the problem you describe. I do not know if it is because I pasted them into the wrong block of cells or if I have otherwise misunderstood. I started by assuming that the =B320-B316 formula belongs in B321, counted up from there to guess that the first formula belongs in B310. Then pasted the block of formulas into B310. I made up some numbers for B310:B312 to see what was happening with B321. I then added an ABS() function in C321, and it seems to work just fine for me.

I would suggest that you spend some more time and create a good sample spreadsheet that will illustrate the problem you are seeing. That will give us a solid starting point for debugging.

@Jose

Good catch. I frequently use circular references/iterative calculations, so I routinely have iteration enabled, so I missed that. The OP did not mention a circular reference, though a circular reference could explain what he/she is seeing. When an inadvertant or erroneous circular reference is present with iteration disabled, you can frequently get garbage results (including functions that should return non-zero values returning 0).

Since I cannot be at all certain that I correctly pasted the formulas into the spreadsheet, my circular reference could be due to my own misunderstanding or misinterpretation of msbeaudry's description of the problem. For now, I will wait until the OP can comment and further clarify the problem, or until someone figures out the list of formulas in post #6.

Originally Posted by msbeaudry

This is the set of formulas relating to the error. I can not attach the full spreadsheet as it is 27MB.

Unless some of those column B cells refer to other columns we only need column B1:B352 - or whatever is the highest row number.