DESIGN IN EXCEL
In this chapter, the procedure for
performing calculations for bubble P for generating P-x-y data in Excel is
shown. The work in this course project has been restricted to only bubble P
calculation. Following are the steps to perform Bubble P calculation.
of Proper Data Base System.
Proper Data Base will
make work easy when we need to call values for particular systems. In this work,
I include three different types of the Data Base systems for my calculation.
critical properties of individual compounds,
binary systems and their Wilson parameters,
comparison with experimental data.
First Data Base contains
critical properties, Antoine constants, acentric factor etc. for the
calculation purpose (Fig. 4.1). There are two columns containing no, first for calling the compounds and
second for calling the values.
Figure 4.1 Data
Base for critical properties of components
Second Database (Fig 4.2)contain
Wilson parameter constants and slope and intersects values to generate parity
plot. First column is for giving unique identity for particular system. And
second and third columns are unique identities of first and second compound in
systems respectively. Last two columns are slope and intercepts to generate
parity plot. Ex no is unique identity for the experimental data and use to call
experimental data from Third Data Base. Third Data Base contains P-x-y data of
Figure 4.2 Data
Base for Wilson parameter constants
properties and parameters,
List all the Parameters
and properties that are remain constant during the calculation. For this
calculation following are the parameters and properties that are constant
during the calculation of particular system.
Table 4.1 Constant
Parameters and Properties.
To call the values of different properties
and parameters vlookup function is
use. Syntax for vlookup can be given
= vlookup (lookup_value, table_array, column_index_no,
In this work, the button
and combo box from the developer tab were used. To use that one first need to
make visible the developer tab which is hide default in any MS word. To make it
visible follow step below for MS Office 2016 or Office 2013
to File ? Options ? Customize Ribbon,
check the box before the developer.
Figure 4.3 Excel Option for developer tab
developer select insert ? combo box,
Figure 4.4 Combo box
selecting combo box draw box of appropriate size, and then follow window will
Figure 4.5 Format control for combo box
In input range
select the cells whose value one want to show in box, in cell link select any blank cell, it will show the number of a
system that is selected from the list i.e. if u select fifth system from the
list (combo box) then it will show 5
in the cell and that is helpful to call the values using vlookup.
To create button, go to developer ? insert ? button,
Figure 4.6 Button tool
After selecting button draw a button of an appropriate
size and after that on window will open to ask for macro but we don’t make it
one so just close the window.
the equations mention in chapter 3 for Tsai and Jan to calculate the ? values and Wilson equation to find ?. To calculate follow the step given
the values of x at uniform intervals,
the values of the parameters required in the calculation of Tsai & Jan EOS
(Calculate till A, B, C).
A, B, C generate the equation for Z using equation (2.31). and compare it
with following equation,
By doing so,
To solve this 3rd
degree polynomial equation following equations are use,
f = ((3*c/a) – (b^2/a^2))/3
g = ((2*b^3/a^3) – (9*b*c/a^2)
h = (g^2/4) + (f^3/27)
i = SQRT ((g^2/4)-h)
j = I ^ (1/3)
k = ACOS(-(g/(2*i)))
L = j*(-1)
M = COS(k/3)
N = SQRT (3) * SIN(k/3)
P = (b/(3*a)) * (-1)
R = -(g/2) + SQRT(h)
S = R ^ (1/3)
T = – (g/2) – SQRT(h)
U = T ^ (1/3)
For h > 0, one real
root and two imaginary
Z1 = (S+U) –
Z2 = (-
(S+U)/2-(b/3a)) + i (S-U) *SQRT
Z3 = (-
(S+U)/2-(b/3a)) – i (S-U) *SQRT (3)/2,
For h ? 0, three unique
and real roots
Z1 = 2*j*COS(k/3)
Z2 = L*(M+N) +P,
Z3 = L*(M-N) +P;
For f, g, h = 0, only one
Z = (d/a) ^ (1/3) * (-1);
For the all possible
values of Z obtain highest real root
is consider to calculate ? for both
the component from the system and same method is used to find ?b for equation
equation (3.2) and (3.3) to find ?.
P1sat and P2sat from Antoine
To calculate bubble P in
excel follow the bellow steps,
a column for estimate P, which will
be found by equation (3.5),
yi using equation (3.6),
a column for calculated P, which is
calculated by equation (3.4),
calculated value on estimate as paste as
column for ?P and ?y. ?P
is difference between estimate and calculated P and ?y is summation of
solver to make ?y = 1 and ?P = 0,
use of solver,
Solve is not installed by
default in Excel. So, to install and use solver use steps given bellow for
office 2013 or above,
to File ? options ? add-ins ? solver add-in, click on GO.
use solver, go to data ? solver,
following window will open,
Figure 4.7 Solver window
set cell as ?P, to value 0 and by
changing cell as copied P cell which
is paste as value only. Add constrain that ?y
cell becomes 1. Run the solver.
Repetitive use of solver
is very tedious and time consuming work. So, to avoid that we have to create macro.
Before that one has to make sure that its macro is enable. For that go to developer ? macro security ? enable all
macro. After that follow steps given bellow,
to developer and click on record macro,
on single solver process,
to developer and click on stop recording,
to developer and click on Visual Basic,
the code and paste it in the sheet where calculation is performed,
can be found from the list of all active sheet shown in top-left penal,
to tools ? reference and tick mark solver,
the code as follow,
creating macro assign it to button mention in step 3.
‘ Macr Macro
Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
SetCell:=”$BN$51″, MaxMinVal:=3, ValueOf:=0,
CellRef:=”$BO$51″, Relation:=2, FormulaText:=”1″
In above coding first
paragraph is add for copy the calculated value and paste as value only so that
solver can be run. In second paragraph first and last line are added fist line
is for reset solver after every titration and last line is to avoid clicking OK every
time solver run. Coding between that is generated by recording macro. Copy
second paragraph and paste it for the number of time one would run it for
different rows or cells. Change the value of cell references with appropriate
values (i.e. if one want to use for next row then $BN$51 becomes $BN$52 and so
on…). Save coding and save excel file as macro enable file.
After generating P-x-y data draw a scatter graphs of
P vs x,
P vs y,
y vs x.
To create comparison plot
we have to generate P-x-y data for the
same value of x that is given in experimental data and compare the calculated y and P values with experimental one. After that, draw various comparison
graphs. (All graphs are mentions in appendix.)
RESULTS AND DISCUSSION
In this excel file
around 25 random systems are included and the result obtained are compared with
the actual experimentally 4 generated data. For Ethanol – Benzene
system at 298.15 K temperature following data are generated and comparison with
actual data generate graphs shown in figure 5.1.
Table 5.1 P-x-y
data for Ethanol – Benzene system
Comparison plots for Ethanol – Benzene system
Just like this
more systems can be calculated in Excel. For around 25 azeotropic and non
– azeotropic system calculation has been
done and results are compiled in table 5.2 and table 5.3 with error in pressure,
y values, parity plot error etc.
Figure 5.2 P-x-y diagram and x-y diagram for ethanol –
Table 5.2 Result
for non azeotropic systems
Table 5.3 Result
for azeotropic systems
In above tables values of
?Y and ?P average error are found by using equation given bellow,
The number in the final
column are show how good the fitting of the curve is, and 1. Excellent, 2.
Good, 3. Moderate, 4. Poor, 5. Very Poor. Rating is done on the bases of how
many points are deviating from the experimental values and how much is the
deviation. i.e., for benzene – toluene systems all the points are perfectly
match except one so, there is possibility that the point is due to experimental
error, thus it is considered as perfect match.
From the above result I
can conclude that,
of the non azeotropic systems with few of the azeotropic systems shows the very
good match with experimental data, but most of the azeotropic systems are
showing deviation from the experimental values.
v More over system with combination of
aliphatic and aromatic compounds are showing more deviation compare to same
More details about this
EOS can be obtain by including more compounds.