Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Dec 2003
    Location
    G, Sweden
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    functions not calculated (excel2000)

    In my worksheet containing a lot of formulas and user functions, I get a lot of #value! instead of calculated results.

    When I select the cell/formula edit bar and click return I get a correct result...?! Why is the cell not calculated? I have calculation set to automatic. F9 or ctrl-alt F9 does not work. activecell.calculate put in the code does not work either...

    Is it an error somewhere that stops excel to procede correctly? Has excel problems finding the right calculation order / tree or what ever it it is called? Any ideas, please!

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: functions not calculated (excel2000)

    Here is a little discusion on this

    Steve

  3. #3
    New Lounger
    Join Date
    Dec 2003
    Location
    G, Sweden
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: functions not calculated (excel2000)

    Thank you Steve!

    I have already tried to consider the tips from the Calculation Secrets page. I THINK that I have avoided the mistakes mentioned there.

    Another way to go would perhaps be to use any audit programs available for analyzing and correcting my spreadsheet and functions. Can you or anyone else recommend any of these?

    Thanks in advance
    Janne

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: functions not calculated (excel2000)

    Not sure this is what you want, but Excel has its own "trace error" function. It is on the "Auditing" toolbar (view -toolbars - customize - toolbars tab -check "auditing")

    Select a cell with an error, click on the "diamond with an exclamation (!)" and it will trace to the cell originating the error.

    You can also trace dependents and precedents with this toolbar.

    Steve

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: functions not calculated (excel2000)

    Steve: When I use Trace Error on my XL2K it appears to do the same thing that Trace Precedents does. In other words, it draws lines to all cells used in the formula, not to the one that is causing the error. Am I doing something wrong?
    Legare Coleman

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: functions not calculated (excel2000)

    Since all the lines are "blue" (not red) The "error" is in the selected cell. The "error" excel traces is the cell that contains the error.

    It is a tool for dependent cells when you want to find the source of the error. The source (origination) of the error is C10, there is no error in any of the dependents.
    XL leaves it still to you to figure our why C10 is giving an error. The error is a type mismatch since "a" (in C1) is not a number.

    If you had a cell that referenced C10, that cell would have an error also, but its error would trace to C10 (the source).

    Steve

  7. #7
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: functions not calculated (excel2000)

    Hi Janne,

    This sounds like your function is non-volatile. If you don't want to use Application.Volatile(True) in your function's code, you could still force the function to recalculate by adding either:
    +NOW()*0
    for numerics, or
    &IF(NOW()=0,"","")
    for strings, to the cells containing your functions.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

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