CHAPTER 4

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.

Step 1.

Generation

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.

1.

For

critical properties of individual compounds,

2.

For

binary systems and their Wilson parameters,

3.

For

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

experiments.

Figure 4.2 Data

Base for Wilson parameter constants

Step 2.

Listing

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

as follow,

= vlookup (lookup_value, table_array, column_index_no,

range_lookup)

Step 3.

Design

Tools,

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

1.

Go

to File ? Options ? Customize Ribbon,

check the box before the developer.

Figure 4.3 Excel Option for developer tab

2.

From

developer select insert ? combo box,

Figure 4.4 Combo box

3.

After

selecting combo box draw box of appropriate size, and then follow window will

open,

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.

Step 4.

Use

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

bellow,

1.

List

the values of x at uniform intervals,

2.

Find

the values of the parameters required in the calculation of Tsai & Jan EOS

(Calculate till A, B, C).

3.

From

A, B, C generate the equation for Z using equation (2.31). and compare it

with following equation,

………. (4.1)

By doing so,

……… (4.2)

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)

+ (27*d/a))/27

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) –

(b/3a),

Z2 = (-

(S+U)/2-(b/3a)) + i (S-U) *SQRT

(3)/2,

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)

– (b/(3*a)),

Z2 = L*(M+N) +P,

Z3 = L*(M-N) +P;

For f, g, h = 0, only one

real root

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

(2.26).

4.

Use

equation (3.2) and (3.3) to find ?.

5.

Calculate

P1sat and P2sat from Antoine

equation

Step 5.

Bubble

P calculation,

To calculate bubble P in

excel follow the bellow steps,

1.

Make

a column for estimate P, which will

be found by equation (3.5),

2.

Calculate

yi using equation (3.6),

3.

Make

a column for calculated P, which is

calculated by equation (3.4),

4.

Copy

calculated value on estimate as paste as

value only,

5.

Make

column for ?P and ?y. ?P

is difference between estimate and calculated P and ?y is summation of

y values.

6.

Use

solver to make ?y = 1 and ?P = 0,

Step 6.

Make

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,

1.

Go

to File ? options ? add-ins ? solver add-in, click on GO.

2.

To

use solver, go to data ? solver,

following window will open,

Figure 4.7 Solver window

3.

Make

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.

Step 7.

Macro

for 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,

1.

Go

to developer and click on record macro,

2.

Perform

on single solver process,

3.

Go

to developer and click on stop recording,

4.

Go

to developer and click on Visual Basic,

5.

Copy

the code and paste it in the sheet where calculation is performed,

6.

Sheet

can be found from the list of all active sheet shown in top-left penal,

7.

Go

to tools ? reference and tick mark solver,

press OK,

8.

Edit

the code as follow,

9.

After

creating macro assign it to button mention in step 3.

Sub Macr()

‘

‘ Macr Macro

Range(“BK51:BK71”).Select

Selection.Copy

Range(“BD51″).Select

Selection.PasteSpecial

Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False,

Transpose:=False

solverReset

SolverOk

SetCell:=”$BN$51″, MaxMinVal:=3, ValueOf:=0,

ByChange:=”$BD$51″, _

Engine:=1,

EngineDesc:=”GRG Nonlinear”

SolverAdd

CellRef:=”$BO$51″, Relation:=2, FormulaText:=”1″

SolverSolve True

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.

Step 8.

Graph

plot.

After generating P-x-y data draw a scatter graphs of

1.

P vs x,

2.

P vs y,

3.

y vs x.

Step 9.

Comparison

plots,

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.)

CHAPTER 5

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

x

0.00

0.10

0.20

0.30

0.40

0.50

0.60

0.70

0.80

0.90

1.00

y

0.00

0.41

0.53

0.59

0.63

0.65

0.67

0.69

0.70

0.74

1.00

P (mmHg)

59.1

90.9

107.5

116.4

121.1

123.6

124.7

125.0

124.4

121.3

95.2

Figure 5.1

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 –

benzene system

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.

CONCLUSION

From the above result I

can conclude that,

v

some

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

systems.

More details about this

EOS can be obtain by including more compounds.