Appendix B. Spreadsheets for Flash Distillation

2.B.1 Regression of Binary VLE with Excel

McCabe-Thiele calculations are easiest to do on spreadsheets if the y versus x VLE data are expressed in an equation. The form y = f(x) is most convenient for flash distillation and for distillation columns (see Chapter 4) if stepping off stages from the bottom of the column up. The form x = g(y) is most convenient for distillation columns if stepping off stages from the top down. Built-in functions in Excel will

determine polynomials that fit the data, although the fit will usually not be perfect. This will be illustrated for fitting the ethanol-water equilibrium data in Table 2-1 in the form y_E = f (x_E). (Note: An additional data point x_E = 0.5079, y_E = 0.6564 was added to the numbers in the table.) Enter the data in the

spreadsheet with x_E values in one column and the corresponding y_E values in the adjacent column.

Highlight all the x-y data. In Excel 2007 in the the menu bar, click on the Insert tab. Select a Scatter chart and then select the icon showing “scatter with data points connected by smooth lines.” This creates a figure of the data plotted as y versus x. If desired, you can add labels, grid lines, and other touches by clicking on Layout in the menu and then following the instructions. If you are not familiar with these techniques, try it step-by-step in Excel.

To find a polynomial that fits the data, first highlight the x-y data. Then go to the Excel tool bar and click on Layout→Analysis→Trendline→More Trendline Options. Choose polynomial as type and in the menu select the desired order of the polynomial. (You can try different orders to find which has the best fit.) Make sure the boxes “Display Equation on Chart” and “Set intercept = 0” are checked. Then click Close.

For the ethanol-water data at 1.0 atm, the best fit was a 6^th-order polynomial. This result is:

(2.B-1) By repeating the steps with the x and y columns reversed, the function xeq = g(y) can easily be generated.

(2.B-2) An alternative to fitting the data is to input the table of data into Excel and then use the “Lookup” function built into Excel to linearly interpolate between data points.

2.B.2 Binary Flash Distillation with Excel

Once an equation form of the equilibrium data is available, it is relatively easy to develop a spreadsheet to solve binary flash problems. We need to input the known values, which we will assume are the mole fraction of the more volatile component in the feed, z, and the fraction vaporized, V/F. Then input the constants for the VLE data in the form y_MVC = f(x_MVC). For this example we will separate ethanol and water at 1.0 atm with an ethanol feed that is 30 mol% ethanol and V/F = 0.4. The VLE equation for ethanol-water at 1.0 atm is given by Eq. (2.B-1). Next, input a guessed value for x_ethanol. Now calculate yeq using this value of xguess from Eq. (2.B-1) and calculate yop from Eq. (2-13). Since yop = yeq at the intersection of the equilibrium and operating curves, eq0 = yeq – yop = 0. Thus, calculate eq0 and use Goal Seek to make it equal zero by changing the value of xguess. [Note: Goal Seek is hidden in Excel 2007. In the spreadsheet go to Data Tab→What if Analysis→Goal Seek.] The resulting spreadsheet with cell formulas is listed in Figure 2-B1.

Figure 2-B1. Spreadsheet with Equations for Binary Flash

The numerical results are presented in Figure 2-B2. Goal Seek was used to set cell B12 to zero by changing cell B8.

Figure 2-B2. Spreadsheet with Numbers for Binary Flash

Since eq0 ~ 0, the answer to this flash problem is x = 0.1555 and y = 0.5167.

2.B.3 Multicomponent Flash Distillation with Excel

If VLE data are available in equation form, spreadsheet calculations can also be used for multicomponent flash distillation. These calculations are illustrated for a chemical mixture that follows Eq. (2-16) for Problem 2.D16. First, the spreadsheet is shown in Figure 2-B3 with the equations in each cell. Cells B3 to B6, C3 to C6, D3 to D6, E3 to E6, F3 to F6, and G3 to G6 are the constants for Eq. (2-16) from Table 2-3. Conditions for the operation are input in cells B7, D7, and F7, and the feed mole fractions are in cells B8, C8, F8, and G9. Eq. (2-16) is programmed for each component in cells B10, B11, B12, and B13. Then the liquid mole fractions are determined from Eq. (2-38) in cells B15 to B18. These four numbers are summed in cell B19. The Rachford-Rice terms from Eq. (2-42) for each component are calculated in cells B20 to B23, and the sum is in B24.

Figure 2.B-3. Spreadsheet for Multicomponent Flash for Problem 2.D16 with Equations in Each Cell.

Goal Seek was used to find the value of V/F that makes cell B25 equal to 0.0 by changing the value in B9.

Cell B25 multiplies cell B24 by 1000 to make the result obtained from Goal Seek more accurate. Goal Seek converged for any guess of V/F from 0 to 1.0. The results are given in Figure 2-B4 for the conditions given in Problem 2.D16: T = 10°C and p = 250 kPa. Since the constants in Eq. (2-3) are for temperature units in °R and pressure units of psia, the temperature is input in °R (cell B7) and pressure is input in psia (cell D7).

Figure 2-B4. Spreadsheet for Multicomponent Flash for Problem 2.D16 with Values in Each Cell.

We could also try not writing the Rachford-Rice terms and use Goal Seek to set the sum of x_i in cell B19

= 1.0. In this problem, Goal Seek works for Σx_i = 1.0 with V/F_guess > ~.5 but does not work for low values of V/F_guess (See Problem 2.H2.). This difficulty reinforces the need to check results from any software package, even one as common and robust as a spreadsheet (Shacham et al., 2008).

Note that there are many other possible approaches to solve this problem with a spreadsheet, and other software tools such as MATLAB or Mathematica could be used (Binous, 2008)