Excel Tutorial II

SPREADSHEETING WITH EXCEL

II. GOALSEEKING, SOLVING, AND GRAPHING

R.M. Felder
Department of Chemical Engineering
North Carolina State University
November 1994
(Revised by E.M. Wilcox in August 1998 and by W.R. Wilcox in April 2004)

Modified from Excel tutorial.

Please email your comments and suggestions

to Professor William R. Wilcox.

Statement of the problem.

See the appendix at the end for the physical basis of the computations to be performed.

Given below are five equations relating seven variables: x, T, p_a*, p_b*, p_a, p_b, and y. Equations 1 and 2 are Antoine equations relating the vapor pressure of pure compounds to temperature. Here p* is the vapor pressure of a or b in Torr, p is the partial pressure of a or b in Torr, T is the temperature in ^oC, x is mole fraction of a in the liquid, y is the mole fraction of a in the vapor, "a" is benzene and "b" is toluene. Ideal mixtures are assumed in both a liquid and a vapor that are in equilibrium with one another.

eqnarray12

We wish to create an Excel spreadsheet that will take as input several values of x, and for each one, find T for which the total pressure p_a + p_b = 760. (This is a sixth equation).

The procedure will be as follows:

1. For a given x, assume a value of T.

2. Calculate log₁₀p_a* and log₁₀p_b* from Eqs. (1) and (2). Then determine p_a* and p_b* by raising 10 to each of the two calculated values.

3. Calculate p_a and p_b from Eqs. (3) and (4).

4. Calculate (p_a + p_b) and compare the result with 760. If the two figures do not agree, try a new value of T and repeat from Step2. Iterate to convergence.

5. When convergence has been achieved, calculate y from Eq. (5).

6. Repeat Steps 1-5 for a set of values of x between 0 and 1. Then plot T vs. x and T vs. y on a single graph.

The calculation will involve generation of the spreadsheet shown in Table 1.

TABLE 1. EXCEL SPREADSHEET FOR EXAMPLE PROBLEM

	A	B	C	D	E	F
1	TXY DIAGRAM FOR BENZENE AND TOLUENE
2
3	P(mm Hg) =	760
4
5	Antoine equation coefficients
6	Compound	A	B	C
7	Benzene	6.90565	1211.033	220.790
8	Toluene	6.95334	1343.943	219.377
9
10	x	T	pa*	pb*	(pa+pb)	y
11
12	0.000	110.6	1784.5	760.0	760.0	0.000
13	1.000	80.1	760.0	292.2	760.0	1.000
14	0.100	106.1	1589.8	667.8	760.0	0.209
15	0.200	102.1	1429.1	592.7	760.0	0.376
16	0.300	98.5	1294.9	530.8	760.0	0.511
17	0.400	95.1	1181.5	479.0	760.0	0.622
18	0.500	92.1	1084.7	435.3	760.0	0.714
19	0.600	89.3	1001.3	398.0	760.0	0.791
20	0.700	86.8	928.9	365.9	760.0	0.856
21	0.800	84.4	865.5	338.0	760.0	0.911
22	0.900	82.2	809.6	313.7	760.0	0.959

For example, when x = 0.900, a value of T = 82.2 substituted into Eqs. (1) and (2) yields p_a* = 809.6 and p_a* = 313.7, respectively. These values substituted into Eqs. (3) and (4) in turn lead to values of p_a and p_b that add up to the target value of 760.0. The corresponding value of y from Eq. (5) is then 0.959.

After this spreadsheet is generated, it will be sorted: Rows 12-22 will be rearranged in order of increasing values of x in Column A. Excel will then create plots of T vs. x and T vs. y that will appear approximately as shown below.

EXERCISE

1. Bring up an Excel window. From the Start menu (button on lower left of screen) select "Programs" and find Excel.

2. Name a new spreadsheet file. Choose the File menu in the Excel window and select Save As. When the "Save As" dialogue window comes up, find the directory in which you want the file to be stored in the "Directories" window (change the drive in the "Drives" window first if necessary), then double click in the "File Name" window and type mod2.xls[CR]. If another window comes up asking for summary information, type anything you want and click on "OK" or "Return." The spreadsheet is now stored under the name "mod2.xls", and that name should appear in the title bar at the top of the Excel window.

3. Enter labels and data in Rows 1-10. Note that [rightarrow] and [downarrow] refer to the arrow keys below the "Insert"..."Page Down" keys.

· In Cell [A1], enter your name, your class and section (e.g., CHE 205, Section 1), and " Excel Tutorial II." Make the typing boldface. (Use the "B" button on the formatting toolbar.)

· Starting in Cell [A3], enter P(mm Hg) = [rightarrow] 760 [CR]. You should see P(mm Hg) = in Cell [A3] and 760 in Cell [B3]. If Column A is too narrow for the contents of [A3] (including the equal sign) to show, click on the column label (A) to highlight the entire column; point to the location right between Columns A and B (the pointer symbol should change to a vertical bar); hold down the left mouse button, and drag the pointer to the right to make Column A wider.

· Starting in Cell [A5], enter the following:

Antoine equation coefficients [downarrow] Compound [rightarrow] A [rightarrow] B [rightarrow] C [CR]

Right-justify the column headings. Point at Cell A6, hold down the left mouse button, drag the pointer to Cell D6, and release. The range [A6:D6] should be highlighted. Click the "right justify" icon on the EXCEL tool bar. Row 6 should now contain Compound A B C, with each label at the right of its cell.
Enter the items shown in Columns A-D of Rows 7 and 8 of the table shown at the beginning of this tutorial:

Row 7: Benzene [rightarrow] 6.90565 [rightarrow] 1211.033 [rightarrow] 220.790

Row 8: Toluene [rightarrow] 6.95334 [rightarrow] 1343.943 [rightarrow] 219.377

Right-justify the compound names by selecting the range [A7:A8] (point to A7, Press and hold, drag to A8, and release), and then clicking the "right justify" icon on the EXCEL tool bar.

Save the spreadsheet with the Ctrl-s command. Hold down the "Ctrl" key and type s. (Alternatively, click the "floppy disk" icon on the EXCEL tool bar.)

Enter the labels in Row 10 (x through y) from Table 1. Beginning in Cell [A10], type

x [rightarrow] T [rightarrow] pa* [rightarrow] pb* [rightarrow] pa+pb [rightarrow] y [CR]

Then right-justify each entry by selecting [A10:F10] and clicking the "right justify" icon.

Enter the values of x shown in Column A of the table. Beginning in Cell [A12] (two cells below the x, type

0 [downarrow] 1 [downarrow] .1 [downarrow] .2 [downarrow] .3 [downarrow] .4 [downarrow] .5 [downarrow] .6 [downarrow] .7 [downarrow] .8 [downarrow] .9 [CR]

(Don't use a formula to enter these values, and don't bother typing the trailing zeros, like the trailing 00 in 0.100.)

Save the spreadsheet.

4. Enter a guess of 100 for the value of T when x = 0.0. In Cell [B12], enter 100 [rightarrow].

5. Enter the formula for p_a* in Cell [C12]. In Cell [C12], type (beginning with an equal sign)

= 10 ^ ($B$7 - $C$7 / (B12 + $D$7)) [CR]

Verify that this formula corresponds to Eq. (1) for T=100. If you entered the formula correctly, the value 1350.491 should appear in Cell [C12].

We anticipate copying this formula into the rest of Column C. When we do so, as we move down the column we will want the cell address [B12] in the formula to change to [B13], [B14], etc., which it will since we used the relative cell address. However, we do not want cell address [B7] in the formula (which contains the Antoine coefficient A for benzene) to change to [B8], [B9], etc., and so we use the absolute address [$B$7].

6. Copy the formula from [C12] to [D12], and change the cell addresses as necessary to calculate p_b*

· Click on Cell [C12]. The cursor box will enclose the cell, and a small dot will be seen at the lower right corner. Point to the dot (the pointer should change to a thin cross), press and hold the left mouse button, drag the pointer to the right to Cell [D12]), and release. This procedure copies the contents of Cell [C12] to Cell [D12]. A very large number should appear in [D12]. (The formula is not yet correct.)

Edit the formula in the formula bar [= 10 ^ ($B$7...], replacing the three 7s with 8s and the C12 with B12. (Aim the pointer just next to the character to be deleted on the edit line, press the left mouse button and drag the pointer over the character in question, release the button, and type the replacement character.) When all the desired changes have been made, hit [CR].

The formula should now read = 10^($B$8-$C$8/(B12+$D$8)), and the value in the cell should be 556.3212. Verify that this is the correct formula (Eq. 2) for calculating p_b* at 100°C.

7. Calculate (pa + pb) [which from Eqs. (3) and (4) equals xp_a* + (1-x)p_b*]. In Cell [E12], type

= A12*C12 + (1-A12)*D12 [CR]

The value in Cell [E12] should be 556.3212. When the desired value of T has been found, this figure will become 760.0.

8. Calculate y [which from Eqs. (3) and (5) equals xp_a*/(p_a+p_b)]. In Cell [F12], type

= A12*C12/E12 [CR]

The value in [F12] should be zero.

Save the spreadsheet.

Print out the spreadsheet: Click on File, Page Setup, Sheet Gridlines and Page, Landscape, and then print out the resulting spreadsheet. Display and print out the formulas by clicking on Tools, Options, View, Window Options, Formulas.

Goalseeking

We are now ready to determine the value of T to give a total pressure of 760 Torr for x = 0 (pure toluene) in Row 12.

9. Vary the value of T in Cell [B12] (the variable cell) until the value of (p_a + p_b) in Cell [E12] (the target cell) equals the specified target value (760).

· Click on Cell [E12].

· Choose the Tools menu and select Goal Seek. A dialog window will come up containing three data entry boxes, with the address $E$12 entered in the first one ("Set cell"). If necessary, move the dialog window (press and hold on the title bar, drag the window to the desired location, release) so that you can see Row 12 of the spreadsheet.

· Click on the To Value box and type 760.

· Click on the By Changing Cell box and type B12 [CR] .

After a little activity, a message box will open labeled Goal Seek Status, informing you that the search was successful and the final value in Cell [E12] is 760.0000018. (You won't always get this close to the target value.) Move the message box if necessary to see Row 12 of the worksheet. You should see a value 110.6253 in Cell [B12], which is the desired value of T.

Click on OK in the Goal Seek Status window. The results of the search are retained in Cells [B12] and [E12], and you can also see the correct value of y (0) in Cell [F12]. (If you had clicked on "Cancel," the old values would have been returned.)

Save the spreadsheet.

10. Copy the formulas in Row 12 into Rows 13-22 and repeat the goalseek calculations.. We will calculate T and y for the values of x in Cells [A13] through [A22], taking as initial guesses for each search the value in Column B.

· Point to Cell [B12], hold the left mouse button down, drag the pointer right to Cell [F12], and release. The selected range should be highlighted, and a small dot should appear at the lower right corner of the highlight box.

· Point to the dot (the pointer should turn into a thin cross), hold the left mouse button down, drag down to Cell [F22], and release. The worksheet should be filled over the target range.

Save the spreadsheet.

Do the calculation for Row 13.

- Click on Cell [E13]. Choose the Tools menu and select Goal Seek.

- Move the dialog window if necessary so you can see Rows 12-22 of the worksheet.

- Click on the To Value box and type 760.

- Click on the By Changing Cell box and type B13 [CR]. You will see a blur in Row 13 as the search proceeds. Click OK on the message box once it appears. For x=1, EXCEL calculates T=80.09999 and y=1.

Do another goalseek, selecting E14 as the set cell, 760 as the target value and B14 as the changing cell. Continue this procedure, increasing the row numbers of the variable and target cells by one each time until you complete the calculation for Row 22.

Save the spreadsheet.

Reformatting the Spreadsheet

You may have noticed that the appearance of the worksheet is somewhat sloppy, with variable numbers of decimal points in the entries (unless you specify otherwise, Excel does not show trailing zeros after decimal points). Before doing any more calculations, let us reformat the worksheet to make it neater.

11. Show the values of x and y with three decimal places, and format Cells [B12:E22] so that all values have one decimal place.

· Select the range [A12:A22] by holding down the left mouse button on [A12], dragging down to [A22], and releasing.

· Click on Format from the Menu bar. Select Cells. This will activate the Format Cells window.

· Click on the Number tab that appears at the top of the window if it is not already highlighted in boldface. This window allows you to change the way numbers appear in the selected cells. (If you click on a column heading you can format the entire column this way.) In the Category window, click on Number and set the number of decimal places to 3. Click on OK.

Select the range [F12:F22]. Follow the above procedure to format the range so that the values are all displayed with three decimal places.
Select the range [B12:E22]. Format it to display the values with one decimal place.
Select the range [D7:D8], and format it so that the values are displayed with three places to the right of the decimal point.

Your worksheet should now look exactly like the one shown at the beginning of this tutorial.

Save the spreadsheet.

Sorting

We now want to reorder the rows of the worksheet so that the values of x in Column A are listed in ascending order, which for x would be 0, 0.1, 0.2,..., 1.0. In spreadsheet jargon, this means we must sort the worksheet in ascending order based on Column A.

12. Sort the portion of the worksheet from [A12] to [F22] in ascending order based on Column A.

· Select the rectangular range [A12:F22] by pointing at [A12], holding down the mouse button and dragging down and right to [F22], and releasing.

· Choose the Data menu and select Sort to bring up the "Sort" dialog window. Move it so that you can see the selected range on the worksheet.

· Point and click the left mouse button in the Sort By box, and select the column in which you had your x values (Column A) if it is not already selected. This means that the rows in the selected range will be reordered so that the values in Column A will be listed in ascending order. (You could also arrange them in descending order by checking the appropriate box in the dialogue window.)

At this point, you could enter another column in the Then By box to be used as the basis for ordering the rows if two elements in Column A have the same value. (This will not occur in this example, so we won't bother to enter a secondary key.)

Click OK. The specified range of the worksheet should now be sorted. (Look at Column A.)

Save.

Note: If you sort a worksheet based on a column containing text entries, the sorting is in alphabetical order of those entries.

Plotting: Windows 95 & Windows NT

We are now ready to generate the graph. You will first generate a line plot of T vs. x, then a second line plot of T vs. y.

13. Create the initial line graph.

· Start the plot generation procedure. Click the "Chart Wizard" icon on the Excel toolbar. (It looks like a bar graph with colored vertical bars toward the right of the toolbar. Alternatively, you could choose the Insert menu and select Chart.) A dialogue window labeled "Chart Wizard" will appear, which will take you step by step through much of the graph generation process.

· Select a chart type and sub-type. Click on XY Scatter under "Chart type:" and then on the plot with smooth curves and no data points under "Chart sub-type." Click on Next> .

· Define the range containing the data to be plotted and title the legend. In the Step 2 dialogue box, click on the "Data Range" tab at the top if it is not already selected. In the "Data range" entry box, type A12:B22 , and click to indicate that your data series are in Columns . A plot of T vs. x will appear in the dialogue window. Don't click on "Next>."

· Still in the Step 2 box, click on the "Series" tab. In the "Name" entry box, type Liquid: T vs. x. Click on Next>.

· Define the chart title and label the axes.

· Click on the Titles tab of the Step 3 window if it is not already selected. Make the following entries in the appropriate boxes (click in each new box rather than hitting [CR]).

- Title the chart TXY DIAGRAM FOR BENZENE/TOLUENE AT 760 TORR

- Title the Category(X) axis Benzene fraction

- Title the Value(Y) axis Temperature (oC). Don't click on "Next>."

Specify whether you want gridlines displayed. Click on the Gridlines tab of the Step 3 window. Insert and remove check marks in each of the boxes to see the effect on the graph, but finish by eliminating all check marks (so that the graph will not display gridlines). Don't click on "Next>."
Specify whether you want a legend displayed. Click on the Legend tab of the Step 3 window. The graph displayed should have a "T vs. x" legend displayed on the right. If you did not want to display a legend (which you generally won't when your graph contains only one curve) you would delete the check mark next to "Show legend." (Don't do it now.) Click on Next>.
Specify that you want the chart displayed in a separate sheet. Click on Finish.

Save

In what follows, "graph window" refers to the large white box containing the graph, title, axis labels, and legend, and "plot area" refers to the interior shaded box that contains the curve. We will now modify the graph to get the desired appearance (shown in the beginning of this tutorial).

14. Resize and format the graph.

Rescale and format the axes. Put the pointer directly on the horizontal (x) axis and wait a moment. When a small box labeled "Value (X) axis comes up, double-click. A "Format Axis" window will appear. In the next series of commands, just go from one tab to another without clicking "OK."

- Under "Patterns," select major tick marks inside, no minor tick marks.

- Under "Scale," make the axis run from 0 to 1, with major tick marks at intervals of 0.1. (Make certain you do not click on the boxes under Auto or the scale settings will revert to their original automatic default values when you click on OK at the end.)

- Under "Font," select "Font style:" = Arial, Regular, 12.

- Under "Number," select Category = Number, Decimal places = <1>.

- Under "Alignment," move the pointer on the semicircle to the top (12 o'clock) position.

- Click on OK.

- Click on the legend for the x-axis and change the font to 14 bold.

Repeat this procedure for the vertical axis, first double-clicking on the axis to bring up the "Format Axis" window.

- Under "Patterns" put major tick marks inside.

- Under "Scale" have the axis run from 80 to 112, with the x axis crossing at 80, and major tick marks at intervals of 4.

- Under "Font" select Arial, regular, 12.

- Under "Number" select Number, 0 decimal places.

- Under "Alignment," leave the pointer at the horizontal (3 o'clock) position.

- Click on OK.

- Click on the legend for the y-axis and change the font to 14 bold. Click on it again so that it becomes horizontal. Highlight the "o" and then click on Format, Selected Axis Title, Superscript, OK.

Remove the legend border and change the legend background color to none. Double-click on the legend box. A "Format Legend" window will come up. Select the "Patterns" tab if it is not already selected.

- Under the "Border" options, click None.

- Under the "Area" options, click on none.

- Click on OK.

Save.

15. Add the second plot (T vs. y) to the graph.

· Select the graph window (click outside the plot area) and then click on the chart wizard icon again. Click Next> to get to Step 2, and click on the "Series" tab.

- Click on the "Add" button near the bottom left of the window.

- Type Vapor: T vs. y in the "Name:" box.

- Type =Sheet1!F12:F22 in the "X Values:" box.

- Type =Sheet1!B12:B22 in the "Y Values:" box.

- Click on Finish. The second graph should now be on the chart.

Save.

Reformat the curves. Point to the top curve (T vs. y), finding a location where a small "Series: T vs. y" box appears next to the pointer. Then double-click to get the "Format Data Series" window. In the "Patterns" window, "Line" options, use the pull-down menus to select a dashed line for "Style," a thicker line, and black (the square at the upper left of the pull-down menu options) for "Color."
Move the legend inside the plot area. Press and hold on the legend, drag it to the upper right of the graph, and release. Change the font size to 12.
Change the background of the graph from gray to white. It could be hard to see black lines on a gray background.

- Double-click on the grey area of the graph to bring up the "Format Plot Area" window.

- Under the "Area" section, select none. Click on OK.

The graph is now complete.

Save.

16. Preview, print and copy.

· Click on File, Print Preview to make certain the graph's appearance is suitable. Then click on File, Print.

· To copy into Word or PowerPoint (not required for tutorial assignment), click on the graph window outside the plot area. Click on Edit, Copy. In Word or PowerPoint, click on Edit, Paste Special, Picture. Use the mouse to move the graph and change its dimensions as desired.

Additional possibilities for Excel, Word and PowerPoint.

· How to enter Greek letters, symbols, superscripts and subscripts

· To add arrows, text, etc. click on View, Toolbars, Drawing.

· To add an equation, click on Insert, Object, Microsoft Equation 3.0. The resulting equation can be moved, enlarged and copied elsewhere.

APPENDIX: PHYSICAL BASIS OF THE TUTORIAL PROBLEM

Data for vapor pressure p* of a pure substance versus temperature T can often be well correlated by the Antoine equation:

The Antoine constants (A, B, and C) for benzene and toluene are given below for T in degrees Celsius (°C) and p* in millimeters of mercury (Torr):

Compound	A	B	C

Benzene	6.90565	1211.033	220.790
Toluene	6.95334	1343.943	219.377

For example, at a temperature of 100.0°C, the vapor pressure of benzene can be calculated as follows:

and so p*(100^oC) = 10^3.1305 = 1350 mm Hg Another way of stating this result is that at a pressure of 1350 Torr, the boiling point of pure liquid benzene is 100°C.

Suppose we have a liquid mixture of benzene and toluene, with x being the mole fraction of benzene in the liquid mixture. That is, if x=0.30, 100 moles of mixture contains 30 moles of benzene and 70 moles of toluene. Further suppose that the mixture is heated in an open flask on a day when the barometric pressure is P_atm = 760 Torr. (Under normal atmospheric conditions, P_atm varies in the range 750-770 Torr.) As the liquid temperature increases, the vapor pressure of benzene (p_a) and that of toluene (p_b). According to Raoult's Law, the liquid in the flask will boil when the temperature reaches a point such that:

where the partial pressures of benzene and toluene in the vapor bubbles are calculated as:

and (p_a*) and (p_b*) are given by the Antoine equation. Moreover, the fraction of benzene in the vapor bubbles that form when the liquid boils is:

The problem we wish to solve is, given a value of P_atm (=760 Torr), calculate the boiling point temperature, T, and the fraction of benzene in the vapor bubbles, y, for a series of benzene fractions in the liquid, x. The procedure is the Goal-Seek calculation in the tutorial.

1. For a given x, assume a value of T.

2. Calculate log₁₀p_a* and log₁₀p_b* from the Antoine equation. Then determine p_a* and p_b* by raising 10 to each of the two calculated values.

3. Calculate p_a and p_b from Eqs. (8) and (9).

4. Calculate (p_a + p_b) and compare the result with 760. If the two figures do not agree, try a new value of T and repeat from Step 2. Iterate to convergence within the accuracy programmed into Goal Seek.

5. When convergence has been achieved, calculate y from Eq. (10).

When we perform this calculation for a number of values of x between zero and one, we can generate the TXY diagram created in this tutorial. Once we have it, vapor-liquid equilibrium calculations become easy to estimate. For example, if we heat a 50% liquid mixture of benzene and toluene (x=0.50) at a pressure of 1 atm (760 Torr), the mixture will boil at 92°C (obtained by going straight up from x=0.5 to the liquid curve and reading the temperature), and the vapor bubbles will contain about 71% benzene and 29% toluene (obtained by going across at 82°C to the vapor curve and reading the fraction on the horizontal axis).