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.

 

[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?

 

[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.

 

[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.

 

[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.

 

[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

 

[placey]

Just wanted to weigh in on this as Damion Young's post nailed it for me. I found that the only cells that had this problem were those that were formatted as Text. Once I changed the cell formatting to General and refreshed the formula (I just d-clicked to enter "edit mode" on the cell then hit Enter) it displayed the result of the formula.

Thanks Damion!!

 

[jonesse2]

This could simply be due to a circular reference, I just made that mistake

Jon

 

[yt_L9]

I'm having a similar issue that seams to stem from copying data from excel 2003 that was dumped there by a SQL database. When pasted into Excel 2007 none of the formulas depending on those values work (they don't work in 2003 either, I did check). As a simple example:

# GROSS NET GROSS NET %
1 $125.00 $75.00 $0.00 $0.00 0%

The formulas for where it states 0 are
Gross : =B2/A2
Net : =C2/A2
% : =1-C2/B2

I don't have a hidden excel running, at least not visible from task-manager and there aren't any circular references. My personal workbook also is working fine.

When I use the insert function button with the formula in place it returns the proper value but it will still show up as a 0 in the cell.

 

[Geet]

Hi All,

Even I faced the same prob. And could find the reason atleast in my case. Not sure if this is applicable for all others who face the same.

In my case, the formula that i had used was a user-defined one. Logic was created with vb. In my logic I had to send certain non-contiguous cells to be processed further. I had sent these cells as "range"(data type) as I came to know range datatype supports both contiguous and non-contiguous cells.

As such the formula "works" but never "updates". I had to forcefully update it by repasting and saving. Setting automatic option, or formatting cell to general, ctrl+` and lot others din work.

Finally once i changed the cells from non-contiguous to contiguous cells the formula started updating. Wen i tried changing back to non-contiguous cells it failed.

So to my knowledge though range accepts and processes non-contiguous cell data successfully issue occurs in formula updation.

I am sure this might not be the scenario in all of your cases but hope it helps atleast a few.

 

[florin_bv]

Hello
I have the same problem in Excel 2007 with autocalculation enabled.
I generate formulas in cells using VBA.
Some cells show correct result, some show nothing.
If I open the file using Excel 2003, Excel Starter or OpenOfice the results are OK ! ! !
I close the file (without saving), open with Excel 2007 and the results are not OK.
(They work with F2 and enter, for each cell)
When I add a new sheet all cells autocalculate and show correct result. Then I delete this new sheet.
Florin