Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Recalc one cell vs. entire spreadsheet

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Clovis, NM
    Posts
    341
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    When I change a cell and press enter, should Excel (97) be recalculating the entire spreadsheet or just that cell + any it impacts? I've noticed on several different files, when I make a change, the status bar shows recalc in progress and counts off % completed. This takes 3-4 seconds and happens even if the cell has no precedents or dependents. I know, get a faster machine. And I don't want to have to toggle automatic recalc. I didn't notice this happening until a week ago. Thoughts or suggestions?

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Clovis, NM
    Posts
    341
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Just wanted to shake this up one time and see if any responses.

  3. #3
    Board Regular Steve Hartman's Avatar
    Join Date
    Feb 2002
    Location
    Houston,Texas
    Posts
    417
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    According to the help file:
    "Whenever possible,Excel updates only those cells dependent on other cells that contain values that have changed...The calculation process may take more time if the workbook contains a large number of formulas, if the worksheets contain data tables, or if the worksheets contain functions that automatically recalculate every time the workbook is recalculated.

    _________________
    Give a man a fish and he'll eat for a day.
    Teach that man to fish and he'll complain about having to work for what he once got free.

    [ This Message was edited by: Steve Hartman on 2002-03-06 11:55 ]

    [ This Message was edited by: Steve Hartman on 2002-03-06 11:56 ]

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Clovis, NM
    Posts
    341
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks Steve, but what I really wanted to know is what formulas or functions would recalc even if the cells that feed them or vice-versa were not involved. The help file is ambiguous (at least to me) in that respect. My file does contain a large number of formulas including some array formulas. Do they automatically recalculate anytime any cell is changed, even if the changed cell has nothing to do with them? Assume A1 is blank and is not referred to in any formula in the entire workbook. If I enter 1 in A1, the entire workbook appears to recalculate, taking several seconds. I'm looking for a workaround to this situation.

    [ This Message was edited by: pilot on 2002-03-06 13:04 ]

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Clovis, NM
    Posts
    341
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    One more time, hoping for additional response.

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-06 13:02, pilot wrote:
    Thanks Steve, but what I really wanted to know is what formulas or functions would recalc even if the cells that feed them or vice-versa were not involved. The help file is ambiguous (at least to me) in that respect. My file does contain a large number of formulas including some array formulas. Do they automatically recalculate anytime any cell is changed, even if the changed cell has nothing to do with them? Assume A1 is blank and is not referred to in any formula in the entire workbook. If I enter 1 in A1, the entire workbook appears to recalculate, taking several seconds. I'm looking for a workaround to this situation.

    [ This Message was edited by: pilot on 2002-03-06 13:04 ]
    For a better understanding of "Volatility" see http://www.decisionmodels.com/calcsecretsi.htm

  7. #7
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Pilot

    Too many volatile functions eg, Now(), TODAY() to name only two of many, can have a re-calculation effect. Array formulas are also notorious for this. If you have a lot of Volatile functions condsider placing them into one group of cells and just reference the needed cell. This prevents using functions like TOday() potentionally hundreds of times. If you have a few array formulae consider using "Database functions" and/or Pivot Tables in their place.

    You might also want to check the "Maximum iterations" you have under Tools>Options>Calculation it is normally 100



  8. #8
    Board Regular
    Join Date
    Feb 2002
    Location
    Clovis, NM
    Posts
    341
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks guys. This was what I needed to hear. I do have lots of INDIRECT functions.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •