# Iterative Function Unstable?

1. ## Iterative Function Unstable?

I thought I had figured this out since I was getting results in my worksheet, then all the numbers were replaced by errors (#NAME, #VALUE). There are several calculation functions in this workbook, all in .xla add-ins so I am unable to upload it here. But I believe the source of my issues come from this function. It is essentially an iterative process producing "guesses" until certain criteria is met. I believe is it also producing a circular reference. At one point this file did work and produced good results, it would take a couple of hours as the calculation was performed on 8000+ rows for 3 different goal scenarios.

Could this code be simplified (require less iterations) or written in such a way to eliminate the circular reference? If someone is willing to look into it, I can provide a Dropbox link that would include the .xls and .xla files.

As the comments in the code suggest, the objective is to mix two air streams to meet certain temperature and humidity goals provided some known values (temperatures). The result will be the volume required from one of the air streams. That's about as much explanation of the calculation as I can provide. I'm still pretty entry level to HVAC calcs . I've identified the function variables, in case it's helpful.

Variables:
Z = altitude (known value)
db1 = outside air dry bulb temp (known value)
wb1 = outside air we tbulb temp (known value)
db2 = return air dry bulb temp (known value)
wb2 = return air wet bulb temp (known value)
Tcfm = total CFM of air available (known value. This function will determine the volume required from the outside air stream. The difference will be the volume of the return air stream)
TMdb = supply dry bulb temp (known value)
TMwb = supply wet bulb temp (known value)
DBorWB = this is determined by a function that results in either "DB" or "WB" (used in the IF statements below)

2. ## Re: Iterative Function Unstable?

I don't see anything in this UDF that should be causing circular references. I can see that it is calling other functions (Twb_mix and Tdb_mix), so we cannot see if there is something in those functions that would trigger a circular reference. If they are as well designed as this one (receiving all necessary inputs through the argument list), then I see no reason for those UDFs to trigger circular references, either.

Could this code be simplified (require less iterations)
As near as I can tell, the "T _mix_0 < Tmatch" loops are using a basic brute force method to find something close to the root of the "function". Possibly, one could use a more efficient root finding algorithm (bisection-like or Newton-like) to reduce the number of iterations required. As I noted, I see nothing in these loops that should cause Excel to see a circular reference. Knowing whether or not the numeric root finding algorithm can be replaced with a direct calculation depends entirely on the nature of the called functions (though I would not be surprised if numerical methods are necessary for something like this).

When I looked at your file from yesterday, I saw the circular references. As I noted yesterday, I think that revising the calculation scheme will require a good understanding of the HVAC engineering behind this -- which you now say that you may not have. I am concerned that, without a decent understanding of the HVAC calculation (independent of programming language), it will be difficult for us to help with the implementation of that calculation in Excel.

As a guess -- is the problem that you have a circular reference, or is the problem that your spreadsheet is not programmed to "clear" the errors once they are generated? I pulled up yesterday's file again to refresh my memory. I see a couple of IFERROR() functions, but I seem to recall that I needed additional IFERROR() functions in the loop in order to "clear" the errors. I have noted elsewhere that I find it necessary with almost all iterative calculations to make sure I have error trapping in all the right places to make sure that errors will be trapped and the loop "reset" when those errors occur. If I remember yesterday's analysis correctly, starting in columns J and K the calculation proceeds down to column AB (and others) which then feeds back into columns J and K (which is why you have a circular reference). When I got it to work for me yesterday, I needed to a couple of IFERROR() functions (I don't remember where, but I recall needing one in column J at least) in order to break out of the error loop. Is it possible that all you need is more strategically located IFERROR() functions?

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