Solved

Excel XP: Calculation problems

Posted on 2003-11-27
13
2,494 Views
Last Modified: 2012-06-27
Hi all
My company just upgraded from Office 2000 to Office XP and now the guys in the Finance dept. is going nuts !!
It seems that Excel XP is not calculating the workbook/sheets properly !!!!!
If the user hits F9 or Shift + F9 does not always calculate the cells !!! We have tried to change the calculation modes (Automatic, manual) but nothing helps.

When the problem occur the only way to calculate a cell is to enter the cell, hit F2 and then hit Enter.

Is this a known problem / bug ? Is there a fix ??

Any ideas is appreciated !!!

regards
Peter

0
Comment
Question by:forsbom
13 Comments
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 9831609
Hi,

Sorry to read of your problem.

Does the cell(s) in question contain a formula or a reference to a user-defined function?

Would it be possible to post the contains of (one of) the cell(s) that is causing your grief?

Thanks.

BFN,

fp.
0
 
LVL 3

Author Comment

by:forsbom
ID: 9831682
There are no user defined formulas in the sheet.

It only happens to cells with formulas and it can be any formula in the cell(s)


Peter
0
 
LVL 8

Expert Comment

by:ampapa
ID: 9834842
Here is a portion of a post on Excel _L archives that might help I realize it's Excel 97 but it might work. I've run into this problem myself and quite honestly I've forgotten how I fixed it???

...
I have a similar problem with one of my workbooks, except it sometimes
happens to many cells.
The way I get it to calculate is to select the entire sheet then do an
Edit/Replace "=" with "="


>
>
> Have an intermittent calculation problem.  My process has two
> (or three)
> books and one addin running at one time, on XL97/NT
> Workstation.  Every
> so often, one of the workbooks developes a "sticky"
> calculation problem,
> which always has at least two characteristics.
>
> 1) Various values are gathered together on "control" sheets in named
> cells, and then those named cells are used as targets by
> other sheets to
> gather values for display and reports.  When the "sticky" calculation
> appears, some of those named cells contain the wrong values.
> Specifically, they contain values that WERE right but when their
> upstream precedent sources changed value, the control sheet values
> didn't update.  Then the errors propagate downstream.
>
> 2) At the same time, the word "calculation" appears in the
> status bar, a
> bit left of center.
>
>
> Pulling down Tools | Options and prowling around the "calculation"
> settings seems to have to effect.
>
> Closing the workbook and reopening it has no effect.
>
> Using the combination Ctrl-Shift-F9 on the cell has no effect.
>
> However, if I delete the formula in the "sticky" named cell (on the
> control sheet) and then type in the formula again from the
> keyboard, the
> problem goes away.  And the word "calculation" disappears from the
> status bar.  Until next time....
>
> I have not found a way to create the problem.  It just sort of appears
> when it wants to...
0
 
LVL 8

Expert Comment

by:Benjamin_Luk
ID: 9835219
Hi, have a try on this
Pulling down Tools | Options  in [View]|window
Select [Formulars]
Exit
See if you Formulars can be viewed as =A4+D5.....

If yes then,
save you workbook
and
Pulling down Tools | Options  in [View]|window
Untick  [Formulars]
Exit

See if you can get the right result

Regards

Ben
0
 

Expert Comment

by:mike_rock_911
ID: 9911759
I don't have Excel-XP so cannot replicate your problem.  Can you tell us what options are available in the Tools>Options>Transition form?  While this has often applied to Lotus=>Excel transitions, your Excel-XP may have something to cover previous Excel versions.

In MS-Word, this is more comprehensive and often the first place to look to correct stange behaviours across versions.

Good luck.
0
 
LVL 3

Author Comment

by:forsbom
ID: 9926748
All the relevant options in Tools>Options>Transition is off.

I found out that a complete rebuild using Ctrl + Alt + F9 solves the problem which by the way is very similar to the problem described by ampapa .... but I still need a more permanent solution then using Ctrl + Alt + F9 each time the user suspects that the calculation is wrong ......


regards
Peter
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Expert Comment

by:eddieashby
ID: 10362288
you could always try tools-options-calculation
and then select automatic.
hope this helps
eddie
0
 
LVL 22

Expert Comment

by:Dreamboat
ID: 10362303
Hi, eddie!

Please read carefully.
The asker already stated in his original question:

>>We have tried to change the calculation modes (Automatic, manual) but nothing helps.

Thanks!

Dreamboat
EE Page Editor
MS Office Technology Channel
0
 

Accepted Solution

by:
PashaMod earned 0 total points
ID: 10371750
PAQed, with points refunded (250)

PashaMod
Community Support Moderator
0
 

Expert Comment

by:Clay_Assassin
ID: 11050964
Did anyone ever find a solution to this problem?

I have the exact same situation.
Reloading ofice 97 doesn't help.

Forcing calculation with a macro doesn't work.

There is no seting to correct.

Anybody figure this out?
0
 
LVL 2

Expert Comment

by:stopher2475
ID: 20112118
I'm looking for a solution myself. I find that pasting the entiresheet as values into a new sheet fixes it but I'd like a better solution
0
 
LVL 2

Expert Comment

by:stopher2475
ID: 20112139
P.S. I mean you can then enter formulas AFTER you do the paste values. Any formulas you paste over turn into their values. This issue seems to have something to do with formatting.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

In case Office 2010 has not been deployed in your environment, this article may be quite useful. In our office, we wanted a way to deploy Microsoft Office Professional Plus 2010 through an automated batch file via logon script. This article is docum…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
The viewer will learn how to make their project stand out over others by learning how to change colors and shapes, add spaces, change directions, and add bullets to their charts.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now