Help!Excel will not calculate formulas- Not in manual calculation

[Lisa]

Hello,

Suddenly my formulas are no longer calculating and when I verify
Options>Calculation, I am in Automatic mode for calculating formulas. F9 does
not work and by switching automatic calculation on and off it does not change.

Also, it seems to depend on the cell. On the same sheet I can have one cell
that calculates a formula and another that does not.

There is no visual basic in my spreadsheet.

Any ideas?

Lisa

 

[Bob Umlas]

Check the format for these cell -- most likely, it's text. Reformat to
General (or some number format) and re-enter the cell. If you have LOTS of
these, copy an unused cell, paste special onto the original range AND also
select "Add")

 

[Ken Wright]

Do you by any chance have a circular reference on your spreadsheet. Take a
look at the status bar at the bottom and see if it says CIRCULAR by any
chance. If it does then you need to resolve the circ before it will calc
properly. If the circ is intentional then you may need to check that
iterations is checked on the calc tab.

Regards
Ken..................

 

[Mike H]

Lisa,

What do you see in these non-calculating cell, is it the formula?
If it is it sounds like they are formatted as text.

If so re-format as 'general' and with the cell selected tap F2 then Enter.

Mike

 

[Rob M]

I also have this problem (except I was in manual calculation). It has
happened intermittently in the past, but last week it became a real nuisance.

The cells contain genuine formulae, displayed as numbers and there are no
circular references. If I press Ctrl-Alt F9, they do recalculate (along with
every other formula), but this can take ages.

In one workbook, about 20 rows calculated in the middle of a sheet, but
those above and below (with identical formulae) did not. Pressing F9 or
changing calculation to Automatic had no effect. Somehow Excel seemed to have
lost the needing calculation flag for these cells.

I was able to save this book and someone else opened it, but there was no
warning that it had not calculated, nor did the cells calculate even after
they pressed F9. Cells would only calculate if you edited the cell and
pressed enter, or changed a precedent cell, or press Ctrl-Alt F9.

It is driving me mad!!!

 

[Roger Govier]

Hi Rob

Sometimes it does get all "hung up"
Try Alt + H (Find and Replace)
Find =
Replace =
Replace All

This can sometimes clear the problem.

 

[Rob M]

Thanks for the comment.

But what causes Excel to get all "hung up"?

How can you know it has happened? (Without checking each time you you change
anything that it has worked - in which case why have Excel at all!)

Is there any way of preventing it happening?

As for "clearing" it, I expect that finding and replacing all "="s with "="s
across all worksheets in all open workbook, while clever, will be slower than
Ctrl-Alt F9, which seems to work. Will it also cover hidden sheets/books?