Formulas not working

[jzkall]

Formulas are not calculating at all (they did previously). This is happening in
several different spreadsheets. It's almost as if formula calculating is
turned off. For example, I have this formula =SUM(E4:E38), which is
returning a zero although there is plenty of data in that range (which are
basic numbers).

I had checked some other posts, and verified that my Calculations are set to
'Automatic, and that numbers are set to to 'General'.

I also opened the same spreadsheet on someone else's computer, and it worked
fine. So it might be specific to my settings, I just can't figure out what it
is I did to change them. Thanks!!

 

[Gary''s Student]

If the calculation mode is Automatic, then check that you are not in equation
display mode:

touch:
CNTRL-`

this toggles equation display mode.

 

[jzkall]

When I do that, it toggles to a view of showing the formulas instead of the
numbers. Still not calculating.

 

[Dave Peterson]

Maybe you could try this:

Select all the cells
Edit|Replace
what: = (equal sign)
with: =
replace all

Excel should see this as a change to each formula and reevaluate each.

 

[jzkall]

Still didn't work. Ugh, this is so frustrating. How can something as simple
as basic formulas cause this much headache!

 

[Dave Peterson]

Ahhh. I should have read the original post.

I bet your numbers aren't really numbers--they're text masquerading as numbers.
Changing the format of a cell doesn't change the underlying value.

If you put
=count(e4:e38)
what do you see?

This should count all the entries that are numeric.

If you see 0 with this formula, then maybe your data has extra stuff in the
cell.

Depending on what it is, there are different ways of cleaning this up.

David McRitchie has a macro:
http://www.mvps.org/dmcritchie/excel/join.htm#trimall
(look for "Sub Trimall()")

And if you're new to macros, you may want to read David's intro:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

===
If that doesn't clean up all the data, you can use Chip Pearson's CellView Addin
to find out what's really in the cells:

http://www.cpearson.com/excel/CellView.aspx

 

[joeu2004]

Formulas are not calculating at all (they did previously). This is happening
in several different spreadsheets. It's almost as if formula calculating is
turned off. For example, I have this formula =SUM(E4:E38), which is
returning a zero although there is plenty of data in that range (which are
basic numbers).

I had checked some other posts, and verified that my Calculations are set
to 'Automatic, and that numbers are set to to 'General'.

I also opened the same spreadsheet on someone else's computer, and it
worked fine.

Since you claim that the same workbook calculates just fine on other
computers, it seems unlikely that there is anything wrong with the
content of the formulas.

Possible exception: if some formulas have external links, the error
could exist in the copy of the linked-to files on your system, but not
on the other computers that you tried.

I wonder: do any of the F9 methods cause the formulas to calculate?

I suspect not. I wonder if you are executing some macros when the
workbook opens that set the enableCalculation property to False. That
will disable all calculation, even manual calculation. When I played
with it, =SUM(...) does indeed return zero. It returns the expected
non-zero result when enableCalculation is set to True. I cannot see
any indication (e.g. in Tools>Options>Calculation) that
enableCalculation is False. In fact, Automatic Calculation is still
selected.

This might not have affected the workbook on other computers either
because their macro security is set not to enable macros, or because
(guessing) your macros might have external References, and the copy of
those external files are okay on the other computers.

Try setting your macro security to Very High, save the workbook, close
all instances of Excel, and reopen the workbook.

HTH.