Modelling with Excel Part 3: Physical Properties

Stephen Hall continues his series on how to use Excel for project engineering. Download the interactive workbook to experiment with as the series develops

Most engineering calculations require material properties such as density and viscosity. The commercial flowsheet simulation packages are underpinned by extensive physical property libraries, vapour-liquid equilibrium data, and property prediction engines. Reliable computations, especially those that involve phase equilibria, are critically dependent on accurate properties and the selection of the best Equation of State. But the everyday problems that I encounter do not need this level of sophistication. I am very happy with using simple correlations that map density, viscosity, thermal conductivity, specific heat, and vapour pressure against temperature. Those properties are enough to solve heat and material balances and to perform hydraulic analyses.

When I create a model that solves problems for any user-defined fluid, I attach a worksheet that contains the coefficient values for physical property equations for a set of fluids. This month I am describing that worksheet and showing how to use it. As a bonus, I am including a set of VBA Functions that give thermodynamic properties for water and steam according to IAPWS-97 (The International Association for the Properties of Water and Steam).

User inputs

For most models the user will enter the name of the fluid, its temperature, and, for gases, its pressure. Other models determine the temperature and pressure, in which case the user will only need to enter the fluid name. Unless you incorporate a databank with the fluid properties, the user will need to enter the properties as well, but with this month’s worksheet that will not be necessary. Download the companion workbook from https://bit.ly/ModellingWithExcel and follow along with the narrative in this article.

The worksheet includes a table with fluids and their properties. You can ensure that the fluid name that the user enters is contained in the databank by giving the column of fluids a name such as “lFluids”. As discussed last month, I use the first letter of named ranges to indicate what type of range it is: “l” for list, “h” for column header, “t” for table, “inp” for user input. Armed with the named list of fluids, use Data Validation (List; =lFluids) on the input cell(s) to present a dropdown list of fluids for the user to pick from.

Data table

In its simplest form, the fluids with their static properties and correlation parameters are listed in a table, named “tFluids” for this example. If the table dimensions are fixed such that the parameters will not move (to a different column in the worksheet) then you can access the parameters and compute the temperature-dependent property using the VLOOKUP function. For a property that is correlated to a linear equation of form y=mx+b, the formula would look something like: “=VLOOKUP(inpFluid,tFluids,2,FALSE)*inpTemperature+VLOOKUP(inpFluid,tFluids,3,FALSE)”, where the correlation parameters “m” and “b” are in columns 2 and 3 of the data table, respectively. Note the fourth argument, “FALSE”, which returns data only when inpFluid exactly matches an entry in the data table. If you omit that argument, the VLOOKUP will return a value for the first entry that is alphabetically adjacent to inpFluid if that fluid is missing from the table and therefore will give an incorrect answer.

VBA user-defined functions (UDFs) provide a much better user experience than the VLOOKUP method. The worksheet entry becomes: “=dens(inpFluid,inpTemperature)” when using the UDF in Listing 1. The data are still in the tFluids table, but with named column headings it no longer matters if the columns are rearranged or if new columns are inserted in the table. Line 10 in the listing finds the row in the table for inpFluid. Lines 20 and 30 retrieve the m and b parameters from the table, and Line 40 computes the result.

The companion workbook contains UDFs for the properties listed in Table 1. The worksheet already includes correlated properties for more than 100 substances. It also includes features for managing the content of the data table. Due to space limitations the VBA code is not included here; you will need to open VBA and examine the code in the workbook to discover all of the details for the features that are described.

The UDFs offer many benefits, including the following:

• Error handling: Natural language error messages can be returned.
• Data checking: The data table can include valid temperature limits that the UDF can check.
• Alternative formulas: The UDF can branch to evaluate certain fluids with individualized correlations. Water and steam properties are computed with the IAPWS algorithms in my UDFs.
• Optional arguments: Pressure is needed to calculate the density of a gas, but may be omitted for a liquid. The formula can omit pressure if the fluid is liquid, just as many native Excel formulas have optional arguments. I use the optional parameter to serve double-duty, to input weight or volume concentrations for solutions and mixtures instead of pressure.
• Units conversions: Viscosity correlations use absolute temperature but I prefer to make inputs with °C. My UDF for viscosity converts the temperature from °C to K before calculating the result. The UDF could also look at a data cell on the worksheet that indicates the units and make conversions accordingly.

Carefully add data to the data table