1 + 1 = 0

[Adrian]

I have a spreadsheet with multiple worksheets. Throughout
the spreadsheet I have sum formulas referencing cells
within a worksheet and from other worksheets. All of a
sudden, some of the sum formulas are returning 0 values
when there are numbers that are being added (an no
negative numbers). I can't find any pattern to which ones
work and which don't.

I can say that:
They are all curency formated cells.
It doesn't matter is I do a formula like =A1+A2 or =SUM
(A1:A2)
The columns summed that are returning 0s are not
referencing cells from other worksheets, but some of the
cells that they are referencing are formulas using cells
from other worksheets.

Thanks for your help

 

[brianwa]

how is the sum cell formatted?

example .25 + .24 = .49.
If you do not format the cell to include decimals excel will round th
number down to 0.

B

 

[Guest]

Thanks, but that's not the problem since the numbers are
in the thousands. Also, since the numbers are formatted as
currency, they go out to two decimal places.

 

[Quandan.nl]

Adrian,

You say some formulas return 0 when adding numbers. Do they return to
the value you would expect them to show after a while? In that you
might be interrupting the recalculation proces of Excel. If formulas
are a little heavy (like external links or custom (VBA) functions)
formulas may return zero's if the recalculation is broken after an
update (addition) has been made within the cells/range they refer to.

You might want to try and add some values again. Wait for the zero's
to show. Then press F9 (recalculation) and wait until Excel has
finished calculating (watch the statusbar at the bottom left of the
Excel window to say 'Ready'... As soon as the statusbar reads 'Ready'
recalculation has been finished.

Have the zero's disappeared now?

Kind regards,

Marcel Kreijne
Quandan - steunpunt voor spreadsheetgebruikers
(Quandan - dutch supportsite for spreadsheet users)
www.quandan.nl