Use of Spreadsheets

1
Use of Spreadsheets for Chemical Engineering
Calculations
David E. Clough Dept. of Chemical
Engineering University of Colorado Boulder, CO
USA David.Clough_at_Colorado.edu
Gjesteprofessor ved NTNU K4 229B clough_at_chembio.
ntnu.no til 10.3.00
2
Fact the spreadsheet program, and
primarily Microsoft Excel, is the most common
computing tool used by the practicing chemical
engineer.

• Other computing tools in common use
• Process simulator
• Aspen, HYSYS, SimSci, ChemCAD
• Statistics
• SAS/JMP, SPSS, Statgraphics,
• Programming numerical methods
• Fortran 90, C/C, Matlab, ...

3
Types of ChE calculations carried out using Excel

• General, small-scale, day-to-day calculations
• formula-based calculations
• data manipulation, graphics statistics
• what-if scenarios and case studies

• Flowsheet calculations
• material energy balances
• vapor-liquid equilibrium
• chemical equilibrium reaction kinetics
• detailed design of process equipment

• Financial calculations
• project economics
• profit/cost optimization

4
Problem-solving capabilities of Excel

• Evaluation of engineering formulas

• Data handling and graphics

• Solution of algebraic equations

• Solution of differential equations

• Optimization calculations

• Applied statistics

• Advanced techniques
• use of Visual Basic for Applications VBA
• use of add-in products
• WhatsBest!, DOEKISS, . . .
• communication with other programs
• HYSYS, LabView, . . .

5
Spreadsheet Basics

• Spreadsheet navigation
• Creating formulas, using names
• Copying and moving formulas
• Using built-in functions
• Creating graphs
• Printing your work

Tutorial materials CHAP1.PDF all example
spreadsheet files and this presentation
SPREADSHEETS.PDF are on FagWeb for this
course Explanatory documents for all
example spreadsheet files available upon request
6
Example Problem Felder Rousseau, 4.6-5
Dehydrogenation of Propane
95 overall conversion of propane
Separator produces 2 streams Product H2, C3H6,
0.555 of C3H8 leaving reactor Recycle balance
of unreacted C3H8 5 of C3H6 in Product stream
7
Example spreadsheet files
Example 4.6-5 from Felder Rousseau
FELDEREXAMPLE1.XLS FELDEREXAMPLE2.XLS
SHIFT.XLS
water-gas shift reactor solves nonlinear
algebraic eqn table-based case study with graphs
FSHT.XLS
flowsheet of process with recycle automated
convergence cash flow analysis optimization
TRIPLE.XLS
material energy balances interpolation of
property tables solution of linear alg
equations optimization of temperature drops
8
Example spreadsheet files
multicomponent VLE calculations Wilson, NRTL and
UNIQUAC models
VLE.XLS
FLASH.XLS
multicomponent flash calculation NRTL model VBA
user-defined function
batch reactor simulation 3 reactions, 6
components compare to experimental data nonlinear
regression of kinetic parameters
BATCH.XLS
9
Example spreadsheet files
FIN.XLS
solution of PDE, heat conduction surface plots
for different materials
REACT.XLS
chemical equilibrium problem 7 nonlinear alg
equations solution using VBA macro
TANK.XLS
calibration of spherical tank live bisection
solution table-based case study user-defined
function alternative
EVAL.XLS ACETIC.XLS
project economics template acetic acid plant
example
10
References
Walkenbach, Excel 2000 Bible, IDG Books, 99
Walkenbach, Excel 2000 Programming for Dummies,
IDG Books, 99
Walkenbach, Excel 2000 Power Programming with
VBA, IDG Books, 99
Gottfried, Spreadsheet Tools for Engineers, Excel
97 Version, McGraw-Hill, 98
Jacobson, Excel 2000 Visual Basic
for Applications, Fundamentals, Microsoft Press,
99