Use of Spreadsheets for Chemical Engineering Calculations

1. Use of Spreadsheets for Chemical Engineering Calculations David E. Clough Dept. of Chemical Engineering University of Colorado Boulder, CO USA David.Clough@Colorado.edu Gjesteprofessor ved NTNU K4 – 229B clough@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 [What’sBest!, 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. Product Sep- ara- tor Feed Reactor Recycle 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