Excel limitations

Considering the very specific nature of the questions, I'm not sure we can make specific suggestions without knowing much more detail about your algorithm and the equations you are trying to solve and so on. At this point, I cannot even be sure if the problem is with the spreadsheet programming of the algorithm or if it is an inherent instability in the algorithm that is present when programmed in any programming language.

In my experience with "circular" calculations, I find that a spreadsheet is much more suited to "linear" calculations. Circular calculations can be tricky to program into a spreadsheet. Assuming equal familiarity with the language, I find that numerical, circular, trial and error type algorithms are often easier to program in symbolic languages (like VBA, Python, C, etc.) or maybe something built for more intense math (like MathCad or Mathematica). The loop and variable structures available in these other languages make it easier to control convergence criteria, test for divergence, and other challenges that are part of these numeric algorithms. However, simply changing programming languages cannot cure the inherent instabilities in the chosen numeric algorithm. If that means that you need to learn a new programming language from scratch, you also need to figure that effort into the task.

Without knowing the specifics of your algorithms, I don't think we can make any specific suggestions. If you will help us understand the algorithm(s) you are using, what causes the instabilities in the algorithms, how you have programmed them into the spreadsheet, and so one, we may be able to make some specific suggestions. If you will tell us what other programming languages you have access to, then we might be able to make suggestions in that direction.

I'm sure a spreadsheet can be programmed to solve this. I'm equally sure that any number of other programming languages could also be used. However, while I don't use them, most of my clients doing this sort of thing use any one of the available process simulators to perform this kind of work. If this is the kind of thing you are going to do frequently, a commercial program like Hysys could be a good investment. I have also seen an open source simulator called dwsim that may be helpful if cost of a commercial product is prohibitive. Unless there is a reason that you must be the coder for the entire set of calculations, I might suggest looking into a process simulator to perform this work.

So, you did not like the idea of using a process simulator for this?

I don't understand what you mean by "it is necessary to program it..I am doing it without any programming". Maybe because, in my mind, what you are doing in the spreadsheet is very much a programming exercise. It is a very different language (a "spreadsheet language") than what we typically think of when we think of a programming language, but it involves all of the same tasks as programming the same thing in other languages.

I believe that this sort of thing can be programmed into the spreadsheet using a spreadsheet programming language. However, the loop structures in the spreadsheet language are difficult to use. When you have multiple loops with more complex logic, it is still possible, but it is even more difficult. I don't think it is absolutely necessary to switch to another programming language, but I think it will be easier in the long run.

As one who generally prefers the spreadsheet language, a strategy I often find myself using is to take identifiable portions of the algorithm and putting them into User Defined Functions (UDFs). For example, you talk about calculating the properties of the gases. If I assume you are using an equation of state (probably one of the common cubic EOS's -- maybe one of those based on VanDerWaal's equation) for these properties, I would be tempted to make a UDF that takes the temperature, pressure, gas composition, and critical properties from the spreadsheet, solves the EOS, and outputs the desired properties back into the spreadsheet. A well designed UDF should be able to seamlessly integrate back into the spreadsheet programming language, in that you can use the function like you would any of Excel's built in functions. It simplifies the spreadsheet language by taking the loops necessary for solving the EOS out of the spreadsheet and isolates them in a single function. If you are interested in learning more about this strategy, you might review this tutorial I made for building simple UDFs: https://www.excelforum.com/tips-and-...uild-udfs.html

You have undertaken a significant project here. It can be done in a spreadsheet. If I were doing this in a spreadsheet, I would expect to use some outside resources (like UDFs) to simplify the spreadsheet programming part.