+ Reply to Thread
Results 1 to 6 of 6

Functions work fine, then don't

  1. #1
    Registered User
    Join Date
    08-28-2015
    Location
    Colorado
    MS-Off Ver
    2013
    Posts
    25

    Functions work fine, then don't

    Trying to simplify an Excel file that I've inherited from an long-gone employee. No one can get it to work so I'm trying to strip down all of the fancy functions and just leaving functions that are actually performing calculations.

    I am having some issues with stability. When I first enter the functions and formulas into the worksheet it seems to work well. The numbers look correct, no errors. But when I click into a cell and click out of it, the correct value then changes to a zero.

    Any advice is appreciated.



    EDIT - SOLVED! I found the answer as a Google search pointed me to another post in this forum. It was just a matter of enabling iterative calculations.
    Last edited by sadele89; 09-25-2019 at 11:40 AM. Reason: SOLVED

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Functions work fine, then don't

    Just an observation. The fact that iterative calculations are seemingly necessary to process yuor formula suggests that there are circular references somewhere.

    In general circular references should be avoided. They may be necessary in some circumstances but in my experience for most tasks there are ways of avoiding them so it may prove worthwhile if you were to identify and change methodology as necessary. Upload the workbook here if you need specific help.

  3. #3
    Registered User
    Join Date
    08-28-2015
    Location
    Colorado
    MS-Off Ver
    2013
    Posts
    25

    Re: Functions work fine, then don't

    Hmm. File is attached. I'm not sure if the macros will upload with it.
    Attached Files Attached Files

  4. #4
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Functions work fine, then don't

    You are correct. The file uploaded is a .xlsx file not a .xlsm file which would contain macros.

    But are macros relevant to your request which was essentially one about iterative calculations?

    The file uploaded does not have any circular references so it doesn't exhibit the behaviour you describe.

  5. #5
    Registered User
    Join Date
    08-28-2015
    Location
    Colorado
    MS-Off Ver
    2013
    Posts
    25

    Re: Functions work fine, then don't

    Before I enabled the iterative calc box, I was also getting circular reference warnings.

    One of the functions looks to be iterative. It increments values until certain criteria is met.


    Thanks for your response! This forum has certainly been a lifeline throughout the years.

  6. #6
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    14,589

    Re: Functions work fine, then don't

    The UDFs appear to be stored in an add-in and not in this file, so, no the UDFs did not upload with your file. I replaced all of the UDFs with a SUM() function, and (after resetting the errors) that allowed the spreadsheet to calculate and converge on something (obviously not the correct answer, but maybe it correctly represents the dependencies well enough??).

    This appears to have something to do with HVAC engineering, and probably will require some expertise to understand exactly what you are trying to calculate here and exactly how the math/engineering calculation needs to be done. Many engineering calculations require iterative methods like this, but it often requires that engineering expertise to know when the circular references are required and when they are not. It seems likely that we will need a quick engineering lesson from you before we will fully understand what is going on here.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 01-04-2018, 01:35 PM
  2. Testing single code, work fine. Put multiple codes in one sheet, one code doesn't work.
    By MayDay1988 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-30-2017, 06:14 PM
  3. [SOLVED] Workbook does not work in excel 2007, but is fine in 2010
    By cath1509 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-06-2013, 02:59 PM
  4. Paste method fails where it used to work just fine.
    By johnjacobson in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-02-2013, 06:53 PM
  5. data should be in sequence and few formulae should work out fine
    By kr61665 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-06-2013, 12:31 PM
  6. Getting this code to work on MAC (ERROR 68) - works fine on Windows
    By LT1511 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-30-2012, 11:09 PM
  7. [SOLVED] Hyperlinks - They seem to work fine - just cannot find the cursor
    By Ron in WA in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-04-2005, 06:07 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1