MALAYSIA
CHE2162 Material and Energy Balances
Week 1 Computer Lab Handout: Microsoft® Excel
Problem 1: Matrix Functions
Learning outcome: Solve material balance problems using Microsoft® Excel’s matrix functions.
Recall the following problem from the Worksheet (Question 3): Acetic acid (CH3COOH)
(A) is to be extracted from a mixture of acetic acid and water (H2O) (B) using 1-hexanol
(C6H13OH) (C), a liquid completely immiscible with water. The process flowsheet and
stream data are shown below.
From the workshop, we have obtained the component mass balance equations as shown:
Acetic acid (A) balance : 0 mC + 0.096𝑚̇E + 0.005𝑚̇R = 46 (1)
Water (B) balance : 354 = 0.995𝑚̇R + 0mE + 0mC (2)
1-hexanol (C) balance : 𝑚̇C = 0.904𝑚̇E (3)
1mC - 0.904mE + 0 mR = 0
Since there are three unknowns, we need three equations to solve the problem! From
above, the problem is completely specified and can therefore be solved. In the workshop,
we did this by hand, but let’s explore some computational methods to solve this problem!
a) On paper (or your tablet), rewrite all three equations in the form of:
𝐴𝑚̇C + 𝐵𝑚̇E + 𝐶𝑚̇R = 𝐷
A =
Include all zero coefficients in your equations.
b) A system of linear equations that can be described in terms of the matrix expression
[𝐴][𝑋] = [𝐵] and its solution is given by [𝑋] = [𝐴]−1 [𝐵]. Based on this knowledge, use
Microsoft® Excel’s matrix functions to solve for the mass flow rates 𝑚̇C , 𝑚̇E , and 𝑚̇R .
matrix A is the coefficients and matrix X is the unknowns.
Page 1
[X] = [A] ^-1 [B]
MALAYSIA
Note:
• You may wonder: “Problem 1 can be easily solved by hand, so why bother using
Excel?” That is true, but what if the problem comes with even more unknowns? It will
be incredibly tedious to solve by hand!
• Take the following problem, for example:
A mixture consisting of six components enters a series of distillation columns and is
successively separated into several different streams. Mass balances are written for
six different unknowns in the problem, i.e., four mass fractions (𝑥1 , 𝑥2 , 𝑥3 , 𝑥4 ) and
two flow rates (𝐹1 , 𝐹2 ), resulting in the following equations:
2𝑥1 + 3.2𝑥2 − 3.4𝑥3 = −2.3976 (4)
𝑥2 + 4.5𝑥3 − 12.7𝑥4 = −2.6635 (5)
𝐹1 − 0.04𝐹2 = 27.64 (6)
0.1𝐹1 + 3.4𝑥1 + 4.55𝑥2 = 13.8228 (7)
−2.3𝑥2 + 0.1𝑥4 − 0.01𝐹2 = −24.2909 (8)
0.5𝑥1 + 0.6𝑥2 + 0.01𝑥3 − 0.9𝑥4 = −0.2988 (9)
• Could you solve this example by hand? Maybe. Would you want to? Probably not.
Let Excel do the heavy lifting!
• Using your newfound skill, try solving the above in your free time. Here are the
answers:
𝑥1 = 0.222
𝑥2 = 0.155
𝑥3 = 0.982
𝑥4 = 0.570
𝐹1 = 123.602
𝐹2 = 2399.048
Page 2
MALAYSIA
Problem 2: Goal Seek
Learning outcome: Set up equations in Microsoft® Excel and apply the Goal Seek function to solve
problems.
The SRK (Soave–Redlich–Kwong) is one of many cubic equations of state used to express
the 𝑃𝑉𝑇 properties of a real gas (more in Week 4). The equation is:
𝑅𝑇 𝛼𝑎
𝑃= − (11)
𝑉̂ − 𝑏 𝑉̂ (𝑉̂ + 𝑏)
where:
(𝑅𝑇𝑐 )2
𝑎 = 0.42747 (12)
𝑃𝑐
𝑅𝑇𝑐
𝑏 = 0.08664 (13)
𝑃𝑐
2
𝛼 = [1 + 𝑚(1 − √𝑇𝑟 )] (14)
𝑇
𝑇𝑟 = (15)
𝑇𝑐
𝑚 = 0.48508 + 1.55171𝜔 − 0.1561𝜔2 (16)
This is a cubic equation in terms of the specific molar volume (𝑉̂ ), where 𝑎, 𝑏, and 𝛼 are
empirical functions of the critical temperature and pressure (𝑇𝑐 and 𝑃𝑐 from Table B.1), the
Pitzer acentric factor (𝜔 from Table 5.3-1), and the system temperature (𝑇). Meanwhile, R is
the gas constant (𝑅 = 8.314 m3 ∙ Pa/mol ∙ K). The tables mentioned above are available in
the Textbook (Felder, Rousseau & Bullard).
Using the Goal Seek function in Microsoft® Excel, solve for the specific molar volume of
CO2 at 𝑇 = 400 K and the pressures (𝑃).
Hint: Rearrange Eq. (11) as 𝑓(𝑉ˆ ) = 𝑃 − 𝑃calculated = 0, and use Goal Seek to set 𝑓(𝑉ˆ ) to 0
by changing 𝑉ˆ . Be careful with units!
Pressure, 𝑃 (kPa) Specific molar volume, 𝑉̂ (m3 /kmol)
10
100
1,000
10,000
Page 3
MALAYSIA
Problem 3: Solver
Learning outcome: Manage data in Microsoft® Excel and perform non-linear regression using the Solver
function.
You are given the following experimental data of vapour pressure (𝑃𝑣 in kPa) and
temperature (𝑇 in K).
T (K) 250 270 310 330 370 390 430 470 510 570 590 610
P (kPa) 57.5 91.6 200.5 266.9 441.1 561.9 827.5 1160.7 1554.9 2254.1 2473.9 2754.6
a) Tabulate the data in two columns on an Excel spreadsheet.
b) Prepare another two columns and convert 𝑃𝑣 to mm Hg and 𝑇 to °C.
c) Prepare another column and calculate the experimental values of ln 𝑃𝑣 .
d) Vapour pressure data are often fit to the Antoine equation, which is written as a function
of temperature (more in Week 5). The equation is:
𝐵
ln 𝑃𝑣 = 𝐴 − (17)
𝑇+𝐶
where 𝑃𝑣 is the vapour pressure (in mm Hg), 𝑇 is the temperature (in °C), and 𝐴, 𝐵, and
𝐶 are empirical constants.
i) Using initial guesses for 𝐴, 𝐵, and 𝐶, set up a column to calculate the predicted
values of ln 𝑃𝑣 for all temperatures in the table using the Antoine equation.
ii) On the same set of axes, plot the predicted and experimental values of ln 𝑃𝑣 against
𝑇 (°C).
iii) Determine the set of constants 𝐴, 𝐵, and 𝐶 that gives the best fit to the Antoine
equation using the Solver function in Microsoft® Excel. Perform non-linear
regression by minimising the sum of squared residuals/differences (SSR) between
the experimental and predicted ln 𝑃𝑣 values, as shown below:
𝑛
2
Minimise SSR = ∑(ln 𝑃𝑣, experimental,𝑖 − ln 𝑃𝑣, predicted,𝑖 ) (18)
𝑖=1
Page 4