some 'sum' formulas when double clicked to check range zero out - formula still there

  • Thread starter Thread starter adam hayes
  • Start date Start date
A

adam hayes

I use excel spreadsheets for cost reporting. I have a summary page an
35 separate schedule pages. the schedules have columns showing cost t
date, cost to complete, budget, over/under spend etc. the totals ar
linked back to the summary page. the summary page shows these total
and then at the bottom adds them all up to show the overall costs. It
these formulas that are acting strangely. Even though the figure
above change when new figures are entered into the schedules, they d
not. And, when I double click on these formulas to check the rang
then press return, the cell goes blank or shows a zero value, eve
though the formula is still there. Do I have a horrible bug or is i
something more obvious? I'm trying to avoid having to start fro
scratch but if the document is infected then I have no option
 
I'd check the easiest first.

Tools|options|calculation tab|make sure it's set for Automatic.

Then check to make sure your values in the cells that the sum() uses are really
numbers. If those cells (not the =sum() cell itself) are formatted as text,
then excel won't add them in =sum().

One way to test:

=istext(a1)
or equivalently =isnumber(a1)
where A1 is the cell that was changed.

In fact, use this formula to get a bunch all at once:

=count(a1:a10)
if it comes back differently than 10 (the number of cells in A1:A10), then
there's some text in that range.

Finally, if all those fail, maybe just making excel recalculate all the formulas
will help.

Do this for each worksheet:

Edit|replace
what: =
with: =
Replace All.

did that help?
 
Adam,

Is the formula surrounded by {}'s? If so it could be an array formula.
I had the same issue and it drove me crazy before I was able to fix
it. If this is the case pres ctrl+shift+enter and excel will know to
treat it as an array instead. Hope that helps.
 
Back
Top