Ms Excel For Chemical Engineers notes (YT - Egypt) PDF

Preview

Summary

Ms Excel Ms ExcelMs ExcelMs ExcelMs ExcelMs ExcelMs ExcelMs ExcelMs ExcelMs ExcelMs ExcelMs ExcelMs ExcelMs ExcelMs ExcelMs ExcelMs ExcelMs ExcelMs Excel...

Description

CAIRO UNIVERSITY FACULTY OF ENGINEERING CHEMICAL ENGINEERING DEPARTMENT

INTRODUCTION TO MICROSOFT EXCEL WITH APPLICATIONS IN CHEMICAL ENGINEERING Eng. Moataz Bellah Mahmoud Mousa Eng. Mohammed Gamal Abdel Nasser

July 2009

Table of Contents Mic Microso roso rosoft ft Ex Excel cel 200 2007 7 Basi Basics cs .................................................................................................................... 1 Inserting Charts ...................................................................................................................................................... 1 Data fitting using least square method ......................................................................................................... 5 Array calculations .................................................................................................................................................. 9 Matrices ................................................................................................................................................................... 10

The Therm rm rmodyn odyn odynami ami amics cs ..................................................................................................................................... 14 Equations of state ................................................................................................................................................ 14 Solving equations of state using excel ........................................................................................................ 16 Vapor Pressure Data Representation Using Equations ........................................................................ 17

Chemi Engin Chemi emical cal Re Reacti acti action on Engin gineering eering........................................................................................................... 21 Batch reactor yield optimization ................................................................................................................... 21 Chemical reaction equilibrium ....................................................................................................................... 23 Example Using Excel ........................................................................................................................................... 24 Regressing rate constants in rate equation from experimental data .............................................. 26 Multiple regression using Excel.................................................................................................................... 26 Non linear regression ....................................................................................................................................... 30 Nonlinear Regression Using Excel .............................................................................................................. 30

Fun Fundamen damen damentals tals o off Ch Chemica emica emicall En Engin gin gineering eering........................................................................................... 33 Material Balance...................................................................................................................................................33 Material balance for non-reactive system .................................................................................................. 35 Material balance for a reactive system without recycle ......................................................................... 37 Material balance for a reactive system with recycle ............................................................................... 39 Energy Balance ..................................................................................................................................................... 43 Energy balance on a non-reactive system .................................................................................................. 50 Energy balance on a reactive system ........................................................................................................... 53

Flui Fluid d Mech Mechanics anics ........................................................................................................................................ 59 Terminal Velocity of Falling Particles .......................................................................................................... 59 Emptying Tank......................................................................................................................................................62 Runge-Kutta method .......................................................................................................................................... 62 Pipeline optimization ......................................................................................................................................... 64

PHASE EQUILIBRIA AND MASS TRANSFER .................................................................................. 68 Introduction ........................................................................................................................................................... 68 Bubble point Calculations................................................................................................................................. 70 Dew point Calculations ...................................................................................................................................... 71 Flash Calculations ................................................................................................................................................ 71 Performing flash calculations using Microsoft Excel ......................................................................... 72

Microsoft Excel 2007 Basics Inserting Charts Microsoft Office Excel 2007 supports many types of charts to help you display data in ways that are meaningful to you. There are many types of charts available in Microsoft excel, and here we will try to show the most commonly used ones in the engineering applications: Line charts Data that is arranged in columns or rows on a worksheet can be plotted in a line chart. Line charts can display continuous data over time, set against a common scale, and are therefore ideal for showing trends in data at equal intervals. In a line chart, category data is distributed evenly along the horizontal axis, and all value data is distributed evenly along the vertical axis.

Pie charts Data that is arranged in one column or row only on a worksheet can be plotted in a pie chart. Pie charts show the size of items in one data series, proportional to the sum of the items. The data points in a pie chart are displayed as a percentage of the whole pie.

Consider using a pie chart when: 

You only have one data series that you want to plot.



None of the values that you want to plot are negative.

Exercise: Search the Microsoft Excel help to find out more information about bar charts and how to use it to draw a Gantt chart

XY (scatter) charts (most used in chemical engineering applications) Data that is arranged in columns and rows on a worksheet can be plotted in an xy (scatter) chart. Scatter charts show the relationships among the numeric values in several data series, or plots two groups of numbers as one series of xy coordinates. A scatter chart has two value axes, showing one set of numeric data along the horizontal axis (x-axis) and another along the vertical axis (y-axis). Scatter charts are typically used for displaying and comparing numeric values, such as scientific, statistical, and engineering data. Consider using a scatter chart when: 

You want to change the scale of the horizontal axis.



You want to make that axis a logarithmic scale.



There are many data points on the horizontal axis.



You want to effectively display worksheet data that includes pairs or grouped sets of values and adjust the independent scales of a scatter chart to reveal more information about the grouped values.



You want to show similarities between large sets of data instead of differences between data points.



You want to compare many data points.

Some of Microsoft Excel charts (unstacked, 2-D, area, bar, column, line, stock, xy (scatter), or bubble chart) provide an easy means of fitting data and getting the equation that fits the data plotted on the graph using a method called Regression. Fitting data is performed by an option called “Add Trendline”

Choosing the right trendline type for your data When you want to add a trendline to a chart in Microsoft Office Excel, you can choose any one of these six different trend or regression types:  linear trendlines  logarithmic trendlines  polynomial trendlines

  

power trendlines exponential trendlines moving average trendlines

Linear trendlines A linear trendline is a best-fit straight line that is used with simple linear data sets. Your data is linear if the pattern in its data points resembles a line. A linear trendline usually shows that something is increasing or decreasing at a steady rate. Logarithmic trendlines A logarithmic trendline is a best-fit curved line that is used when the rate of change in the data increases or decreases quickly and then levels out. A logarithmic trendline can use both negative and positive values. Polynomial trendlines A polynomial trendline is a curved line that is used when data fluctuates. The order of the polynomial can be determined by the number of fluctuations in the data or by how many bends (hills and valleys) appear in the curve. An Order 2 polynomial trendline generally has only one hill or valley. Order 3 generally has one or two hills or valleys. Order 4 generally has up to three hills or valleys. Power trendlines A power trendline is a curved line that is used with data sets that compare measurements that increase at a specific rate. You cannot create a power trendline if your data contains zero or negative values. Exponential trendlines An exponential trendline is a curved line that is used when data values rise or fall at constantly increasing rates. You cannot create an exponential trendline if your data contains zero or negative values. Moving average trendlines A moving average trendline smoothes out fluctuations in data to show a pattern or trend more clearly. A moving average uses a specific number of data points (set by the Period option), averages them, and uses the average value as a point in the line. For example, if Period is set to 2, the average of the first two data points is used as the first point in the moving average trendline. The average of the second and third data points is used as the second point in the trendline, etc..

NOTE: Choosing the type of trendline in many cases depends on the case whose data is being fitted, i.e. you may know that your data must fit linear or logarithmic or whatever trendline before they are plotted. A trendline is most accurate when its R-squared value is at or near 1. When you fit a trendline to your data, Excel automatically calculates its R-squared value. If you want to, you can display this value on your chart. The table below summarizes the types of trendlines and the final forms of equations: Use this type

To create

Linear

A linear trendline by using the following equation to calculate the least squares fit for a line: where m is the slope and b is the intercept.

Logarithmic

A logarithmic trendline by using the following equation to calculate the least squares fit through points: where c and b are constants, and ln is the natural logarithm function.

Polynomial

A polynomial or curvilinear trendline by using the following equation to calculate the least squares fit through points: where b and

Power

are constants.

A power trendline by using the following equation to calculate the least squares fit through points: where c and b are constants. This option is not available when your data includes negative or zero values.

Exponential

An exponential trendline by using the following equation to calculate the least squares fit through points: where c and b are constants, and e is the base of the natural logarithm. This option is not available when your data includes negative or zero values.

Moving average

A moving average trendline by using the following equation:

Data fitting using least square method Microsoft Excel proved to be very effective in curve fitting, but it does so for five forms of equations which were mentioned previously. If the equation that you need to fit your data to is not one of these five forms then Microsoft Excel will not be able to fit your data automatically using the Trendline option. In these cases the user must be able to fit his data to the form he needs by himself. There are many methods to do so, the most common and easy to use is what so called “Least square method”. To understand this method let‟s see the next example: Consider the following experimental data relating any two variables (say x and y) X 3.4 Y

7.1

16.1 20.0

9.59 5.29 3.63 3.42

23.1 34.4 40.0 44.7 65.9 78.9 96.8 115.4 120.0 3.46 3.06 3.25 3.31 3.50 3.86 4.24 4.62

4.67

And that from your knowledge about your experiment you know that the relation must take the form: Y=aX+b/X+c And you now need to fit these data to that equation to get the values of a, b and c, also to calculate the R-squared. This method is based on a simple idea, which is assuming values for the variables a, b and c, then calculating values of Y‟s based on the assumed values, then comparing the calculated and the real values of Y‟s, and finally changing the values of assumed a, b and c till the difference between the real and calculated vales of Y‟s is minimum. This is simply done by building a simple table, two columns for the experimental data available (X‟s and Y‟s ), another column for the calculated values of Y‟s ( based on the values of X ).

Remember to add the dollar signs on typing the names of cells containing assumed vales of a, b and c before dragging. After dragging the table will be in the form shown below:

Now it‟s now required to minimize the difference between the calculated and the real values. So a new column is introduced to calculate the difference between the calculated and the real values.

Now it‟s required to minimize all the values in the last column by varying the values of the variables a, b and c. Microsoft Excel has a tool that performs this form of iterations which is “Solver”. It‟s available under the Data toolbar in the analysis tab.

On clicking the solver tab a window will appear having the shape:

This tool can change the value of multiple cells to set the value of ONLY ONE cell to a maximum, minimum or a specific value. And we need now to change the value of the three cells (a, b and c) to set the values of 13 cells to zero, which is not available using this tool. A trick can be done to avoid this problem. If the sum of the differences is calculated and then is set to the value of zero then consequently the values of all the 13 cells will be minimized, so a cell for the sum values in the last column is set and calculated. And is now the cell that will be set to zero. But remains a small problem, you may notice that some of the values in the last column are positive and some are negative, so the summation may be zero but the entire cells in the column may not be zeros. So a new column is added where the SQUARES of the values of the difference column are calculated, so all the values will be positive and this problem is no more present. And that‟s why this method is called Least Square Method. Finally the table will have the form:

Now we are now ready to use the solver.

By clicking solve the final results appear:

It‟s now clear that the values in columns D and E are minimized. Now to ensure the accuracy of this fitting the R-squared value must be calculated. R-squared value is calculated from the following relation:

Then finally the shape of the table will be as follows:

And the R-squared value is found to be 0.9967 which is a very good fit.

Array calculations An array formula is a formula that can perform multiple calculations on one or more of the items in an array. Array formulas can return either multiple results or a single result. For example, you can place an array formula in a range of cells and use the array formula to calculate a column or row of subtotals. You can also place an array formula in a single cell and then calculate a single amount. To understand the concept on arrays well, let‟s consider the least square method example discussed previously. On calculating the value of the sum of square of the differences between the calculated and the real values we had to add a column to calculate the differences then another column to calculate the square of the differences, and finally a cell to calculate the summation of the squares. Array calculations provide a quicker means to do these 3 or 4 steps in only one step. In any cell type the following formula “=SUM(C2:C14-B2:B14)” then press Ctrl+Shift+Enter and compare it with the value in cell D17, you will find that they both have the same value, this means that this formula calculated the difference between the values in two columns and got the sum of these values. On typing the following formula: “=SUM((C2:C14-B2:B14)^2)” you will be able to calculate directly the sum of squares of the differences.

Matrices Matrix calculation is a special type of array calculations, as Microsoft Excel deals with matrices as arrays. Microsoft Excel can perform many matrices operations such as adding, subtracting, multiplying matrices, also getting the inverse of a matrix and the value of its determinant. Before discussing how these operations are performed using Microsoft Excel, it‟s recommended to revise quickly the matrix operations and calculations. Consider a system of linear equations like : a1X+b1Y+c1Z=L a2X+b2Y+c2Z=M a3X+b3Y+c3Z=N where X,Y and Z are variables a1,b1,……., b3,c3 are their coefficients ( constants ) L,M and N are constants And it‟s required to solve these equations to get the values of X,Y and Z. One of the methods of solving this system of equations is using Matrices. This can be simply performed by putting the system of equations in the form of matrix as follows a1

b1

c1

a2

b2

c2

a3

b3

c3

Which takes the general form:

X ×

Y Z

L =

M N

AX=B

Before solving such a system, we must check first if it‟s solvable or not, which can be verified by calculating the value of the determinant of the matrix “A”. If its value ≠ 0 then it‟s solvable, if its value=0 then it‟s not solvable. After performing this check the system has to be solved. This was done by performing the following steps: AX=B -1

A AX=A-1B X=A-1B So getting the final solution requires first getting the inverse of the matrix “A”, then multiplying it by matrix “B” to get the final solution. NOTE:on multiplying two matrices, the number of columns of the first matrix has to be equal to the number of rows of the second one.

Now, consider a set of linear equations that we have to solve using Microsoft Excel, like: X-Y-Z+3 L=1 2X+4 Y+3 Z+L=2 2.25 X+Y+2 Z+2 L=3 X+1.5 Y+Z+2 L=4 This will take the matrix form as follows: 1

-1

-1

3

2