?
Solved

Excel XP: Calculation problems

Posted on 2003-11-27
13
Medium Priority
?
2,503 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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
 

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

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

764 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