Formulas not calculating in Excel in Office 2007- all set to autom

[Mark]

I have Office Enterprise/2007 and recently, no formulas are calculating at
all. I have the settings for calculation on automatic and the cells are NOT
set to display the formula instead of the result. There may be some kind of
bug that seems to be impacting the program badly and I think it may have
something to do with recent updates to Vista SP1 and any Office updates as
well.

I ran the diagnostics in Excel and they all came up clean. At this point, I
don't know what can fix it.

Anyone know of this problem and how to fix it? Excel is pretty worthless
without formulas, I can make lists in Textpad... Thanks.

 

[Jan Karel Pieterse]

Hi Mark,

I have Office Enterprise/2007 and recently, no formulas are calculating at
all. I have the settings for calculation on automatic and the cells are NOT
set to display the formula instead of the result.

Is this a problem with all workbooks, or just one (a couple)?

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

 

[Mark]

It happens in multiple documents and now sometimes happens and sometimes does
not. I tried a simple calculation in the original document and at first it
didn't work, then after closing and reopneing the document a few times, the
formulas astarted to work. Then the somewhat complicated forumlas worked
until I modified them. Now again no forumlas will work. It seems like some
kind of flighty bug causing this since it's sporadic. Any thoughts?

 

[Bob Flanagan]

Mark, have you tried pressing ALT-CTL-F9? It should force all formulas to
recalculate.

Bob Flanagan
Macro Systems
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

 

[Mark]

Yes, done that a few times, but no results. Something seems to be imparing
the ability to actually calculate the formulas. I've tried different add-ins
to see if they impact it, but no change. I consider myself an expert in
Excel, so this problem is pretty disconcerting.

In a fresh document, I can get simple forumlas like =1+1 to calculate, but
when I paste a slightly more difficult formula like:

=C2&", "&D2&", "&E2&", "&F2&", "&G2&", "&H2&", "&I2&", "&J2

-to concatenate data from multiple cells (as suggested by a help topic), it
just leaves that formula in the cell. the formula 'concatenate' doesnt' work
at all. It really acts like I have it set to just display the formula and
not result, but I have rechecked that many times. Everything is formated as
text and all of the normal checks are in place. Any other suggestions to
check? Thanks for trying to help.

 

[Jan Karel Pieterse]

Hi Mark,

It happens in multiple documents and now sometimes happens and sometimes does
not. I tried a simple calculation in the original document and at first it
didn't work, then after closing and reopneing the document a few times, the
formulas astarted to work. Then the somewhat complicated forumlas worked
until I modified them. Now again no forumlas will work. It seems like some
kind of flighty bug causing this since it's sporadic. Any thoughts?

Does starting Excel in safe mode make a difference (hold down control key when
you start Excel)?

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

 

[Mark]

No difference in results when opening in safe mode. I am noticing some odd
things occuring (regardless of mode):

The main formula I am using (=c2&","&d2&","...etc.) is the same in all
cells, save for the row and number of coumns included. However, some
formulas display the correct results while others just show the formula.
When copying into a new document, the same results occur, as if those
non-working cells or formulas have some overall corruptness to them.

This document is saved as .xlsm for use in the new Office format, I'll try
converting to Excel 2003 to see what happens, but this still doesn't solve
the problem of Excel Enterprise not being able to calculate formulas.

 

[Jan Karel Pieterse]

Hi Mark,

The main formula I am using (=c2&","&d2&","...etc.) is the same in all
cells, save for the row and number of coumns included. However, some
formulas display the correct results while others just show the formula.
When copying into a new document, the same results occur, as if those
non-working cells or formulas have some overall corruptness to them.

This document is saved as .xlsm for use in the new Office format, I'll try
converting to Excel 2003 to see what happens, but this still doesn't solve
the problem of Excel Enterprise not being able to calculate formulas.

What happens if you select the formula in the formula bar and copy the formula,
not the entire cell? (if there is a corruption, copying the cell may very well
include the corruption in the paste!)

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

 

[Mark]

No difference whether it is copied by cell or cell contents. Actually if I
go into a cell that was working and edit the formula in almost any way, the
cell stops calculating and leaves the formula displayed- same as if I type in
a new formula most of the time. This appears to be something big going on
and not a simple fix.

 

[Jan Karel Pieterse]

Hi Mark,

No difference whether it is copied by cell or cell contents. Actually if I
go into a cell that was working and edit the formula in almost any way, the
cell stops calculating and leaves the formula displayed- same as if I type in
a new formula most of the time. This appears to be something big going on
and not a simple fix.

Please check the cell's number format, is it by any chance set to Text?
If so, that would explain the behaviour. Set the cell number format to general
and all should be well.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

 

[Lars Stormlund]

We have the same error for multiple users. All set for autocalculation, but
Excel seems to completely ignore this. Even a "=2+2" displays 0. Editing the
cell remedy the situation, but only for that particular cell.

The files are in Excel 2007 format running off a 64-bit Windows 2003 R2
Terminal Server farm. The errors are daily reported from multiple users, and
does not seem related to ressource strain or specific Excel sheets.

All Office updates are installed.

It "seems" that this error has begun during May - have not investigated
Office updates yet ...

Any ideas will be greatly appreciated.

 

[M]

We're experiencing the exact same problem in our office (just on a lower
scale). I've had a thorough read through this thread in the hope of isolating
a solution but unfortunately all I found was the same failed attempts at
solving this bug.

Our office comprises of many experienced Excel users (most with 5+ yrs
experience), VBA programmers and none of us have been able to find a solution
to this frustrating problem, apart from adhoc cutting+pasting.

The purpose of this post is to register my interest in this thread and to
articulate the magnitude of this problem.


M

 

[judy]

yes, we are having the same problem in our office as well, although it just started this week for us. Has anyone found a solution yet?

 

[Beverly]

yes, we are having the same problem in our office as well, although it just started this week for us. Has anyone found a solution yet?

I came here hoping to find a solution to this too. It just started
happening to me today.

 

[T.J.]

We are having the same problem using Excel 2003. What we found was that the
problem only occurs if you open up windows exporer and double click directly
on the Excel document. If you open Excel first and then go to file-->open to
browse to the file, the calculations seem to work just fine each time. We
have 4 people in our company that randomly have this problem. So far after 2
weeks of opening Excel files this way, no one has had a problem. I thought
maybe ths information would work with Excel 2007 also as a pretty good work
around. I hope it helps.

 

[Damion Young]

I have just spent an hour or so struggling with this - in my case, it was two things:

1. spaces in formulae
2. setting the cell format to 'General' if it was 'text'

The weird thing is that Excel allows you to edit the formula in the Function Arguments dialog box but still won't display the result....incredibly anoying.

Hope this helps

 

[Smitten Kitten]

This happens to me too. As far as I've been able to determine it only happens when there is an invisible excel.exe in your proces list. Use the taskmanager to kill the process, or reboot, and in my case the problem was solved.

Another effect of this is that the personal macro's don't load.

Good luck.

 

[Jay Wynn]

I also encountered the problem with formulas/cells not calculating. Discovered that there were a series of circular references embedded in the sheet. Killed those by hard-coding values, and the remaining cells calculated just fine.

EggHeadCafe - .NET Developer Portal of Choice
http://www.eggheadcafe.com/default.aspx?ref=ng