×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Why does a selection trigger the calculation state?

Why does a selection trigger the calculation state?

Why does a selection trigger the calculation state?

(OP)
Hello,

I have a spreadsheet with many calculations (ASME UHX calculations). Because of the number of calculations, it takes 10 to 20 seconds to see the complete results.

Because of it's slowness, I turned automatic calculation off, and now I press a button to force calculation.

The reason because I do this, is that Excel seems to trigger the calculation state to 'Pending', even if only a depending cell is selected.

I would think the state should be changed to 'Pending' only after the cell is really changed. Or maybe if the cell is edited (even if nothing is really changed).

Is it possible to alter this behaviour?
In my opinion, the state shouldn't be changed to 'pending' only when a cell is selected.
 

RE: Why does a selection trigger the calculation state?

This sounds a bit strange to me.  You can get some funny effects with recalc=manual when the spreadsheet is big enough to exceed the limits of Excel's dependency tree.  There are circumstances where the RecalcPending indicator comes on almost permanently, and it might be that you have struck ths problem:  a search via Google Groups will throw up a few discussions on this.

There are also circumstances where the indicator will never come on.  One such is if you use the OFFSET function, perhaps to establish dynamic arrays.

A very informative article on Excel's recalc behaviour can be found at
http://www.decisionmodels.com/calcsecrets.htm
This will not directly answer your question, but it might trigger a useful thought or two.

These comments apply to Excel 2002 and Excel 2003.  I have no experience of Excel 2007.

 

RE: Why does a selection trigger the calculation state?

(OP)
I've searched the internet quite some time to find answers to my 'strange' findings.

The calculation state shows 'calculated', so everything is fine. Now I can click on every blank cell, cells with static text, ..., the calculation state keeps saying 'calculated'.
But, is I dare to click on a cell that contains a value that is used somewhere in a calculation, the calculation state switches to 'pending'. Nothing has been changed, only selected.
This is not what behaviour should be according to your link.

I just tested it with a small excel sheet, and the behaviour is more or less like described in your post, Denial.

I now think there might be a volatile function hided somewhere in the sheet. I will search for them now.
Thanks for the interesting link!

Jeroen.

RE: Why does a selection trigger the calculation state?

You might try Tek-tips.com - a sister to this forum with some really smart people there....

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources