Solved

Excel XP: Calculation problems

Posted on 2003-11-27
13
2,500 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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
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 create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

717 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