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, August, 1998)

Statement of the problem.

Given below are five equations relating seven variables: x, T, pa*, pb*, pa, pb, and y.

We wish to create an EXCEL spreadsheet that will take as input several values of x, and for each one, find T for which (pa + pb = 760). (This is a sixth equation).

The procedure will be as follows:

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

2. Calculate log10pa* and log10pb* from Eqs. (1) and (2). Then determine pa* and pb* by raising 10 to each of the two calculated values.

3. Calculate pa and pb from Eqs. (3) and (4).

4. Calculate (pa + pb) 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.

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.

For example, when x = 0.900, a value of T = 82.2 substituted into Eqs. (1) and (2) yields pa* = 809.6 and pa* = 313.7, respectively. These values substituted into Eqs. (3) and (4) in turn lead to values of pa and pb 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 as shown in Figure 1.

In the tutorial exercise that follows, we will give instructions for running the modern version of EXCEL (which runs on Windows NT and Windows 95) and the old version (which runs on SPARCstations under a Windows 3.1 emulator like SoftWindows or WABI). We strongly suggest that you use a machine that runs the new version, however--the old one is very slow and generally inferior.

EXERCISE

1. Bring up a EXCEL window

• Windows 95 or Windows NT: Open EXCEL from the Start menu (button on lower left of screen, select "Programs" and find EXCEL) or the Office toolbar.
• Windows 3.1: Hold middle mouse button down to bring up the Application Menu, select MS-Windows Emulators - WABI. When WABI has opened (it may take a VERY long time), go to the Program Manager window, double click on the MS Office icon, then, double click on EXCEL.) After another considerable interval, an EXCEL window will appear.

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 pa* 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 pb*

• 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 7's with 8's 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 pb* at 100°C.

7. Calculate (pa + pb) [which from Eqs. (3) and (4) equals xpa* + (1-x)pb*]. 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 be 760.0.

8. Calculate y [which from Eqs. (3) and (5) equals xpa*/(pa+pb)]. In Cell [F12], type

= A12*C12/E12 [CR]

The value in [F12] should be zero.

Save the spreadsheet.

Goalseeking

We are now ready to begin the trial-and-error search for the correct value of T corresponding to x = 0 in Row 12.
1. Vary the value of T in Cell [B12] (the variable cell) until the value of (pa + pb) 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 dialogue 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.

2. 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 (110.6253).

• 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 print trailing zeros after decimal points). Before doing any more calculations, let us reformat the worksheet to make it neater.

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

None of the format codes at the window on the right matches what we want (three decimal places), so we create our own code.

• Double-click on the Code entry box at the bottom of the dialogue window. When the box is highlighted, type 0.000 [CR].

All the values in the selected range should now have the desired appearance.

• 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 (Code = 0.0).

• 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 generate plots of T vs. x and T vs. y (as in the graph at the beginning of this tutorial). Before doing this, it will be necessary 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.

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

Click on Windows 3.1 if you are working on a SPARCStation or another computer running an old version of EXCEL.

1. Create the initial line graph.

• Start the plot generation procedure. Click the "graph" 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 MM HG
-- Title the Category(X) axis   Benzene fraction
-- Title the Value(Y) axis   Temperature (Deg. C). 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 whether you want the chart displayed on your worksheet or in a separate sheet. Make sure "As object in: Sheet 1" is selected in the Step 4 window. Click on Finish.

A small ugly graph will appear somewhere on your worksheet.

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

2. Move and resize the graph.

• Move the graph. Point to a blank space inside the chart window but outside the plot area, reasonably far away from any text or object, and wait for a second or two. When a label appears that says "Chart area," Press and hold, drag the window to a suitable location on the worksheet (to the right of the data or below it), and release.

• Resize the graph. You should see black dots at the corners and at the midpoints of each border of the graph window. If you don't, click on the window (outside the plot area) to make them appear. Point to the dot at the bottom right corner, finding a location where the pointer turns into a two-headed arrow. Press and hold, and drag down and to the right to enlarge the window enough for everything to be clearly displayed. (Don't worry about getting it perfect--you can always change it again later.)

To resize the graph only vertically you would use the bottom middle dot, and to resize it only horizontally you would use the right middle dot.

• Save.

• 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.
-- Under "Font," select "Font style:" = Arial, Regular.
-- Under "Number," select Category = Number, Decimal places = <3>.
-- Under "Alignment," move the pointer on the semicircle to the top (12 o'clock) position.
-- Click on   OK.

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.
-- Under "Font" select Arial, regular.
-- Under "Number" select Number, 0 decimal places.
-- Under "Alignment," leave the pointer at the horizontal (3 o'clock) position.
-- Click on   OK.

• Remove the legend border and change the legend background color to white. 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 white (lower right box).
-- Click on OK.
-- Save.

3. 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.
-- Save.

The second graph should now be on the chart.

• Reformat the second curve. 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" 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 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 white. Click on OK.
-- Save.

The graph is now complete.

4. Preview, print, and exit.

• Deselect the graph (click anywhere outside the graph window) and select "Print Preview" under the "File" menu.

• If the worksheet including the graph does not fit on a single page, close the preview window, make the graph smaller, and repeat. When everything is on one page, close the preview window and print the worksheet (select "Print" under the file menu).

• Exit from EXCEL. Choose "Exit" under the "File" menu, or click on the X button at the upper right corner of the EXCEL window.

Click on Physical basis of the tutorial to see a description of the vapor-liquid equilibrium problem that this spreadsheet was designed to solve. Otherwise, you may exit from the tutorial.

1. Create the initial line graph.

• Specify the range containing the data that you want to plot. Select the range A12:B22 on the worksheet by clicking and holding on [A12], dragging the pointer to [B22], and releasing.

• Start the plot generation procedure. Scroll the worksheet down so that Row 19 appears at the top. (This is to show the intended location of the graph, which will be below Row 22.) Click the "graph" icon on the EXCEL toolbar. (It looks like a bar graph with vertical bars toward the right of the top toolbar.)

• Define the graph location and size. When you click on the graph icon the pointer will become a thin cross. Move it to a point in Column A somewhere below Row 22, Press and hold, drag the pointer down and right to somewhere in Column F to define an approximate square, and release. (Don't worry about getting it perfect--once you have created the graph you may always change its size and position.)

A dialogue window labeled "Chart Wizard" will appear, which will take you step by step through much of the graph generation process. It will first ask if the range you selected is the range you actually want (A12:B22). Click   Next> .

• Select a chart type. In the Step 2 window, click on XY Scatter chart type, and then click on Next> . In the Step 3 window, click on the plot with smooth lines and no data points (Sub-type 6), and then click on   Next>

• Define the location of the horizontal axis data. In the Step 4 Window, a sample chart will come up showing what the plot will look like, and several selection boxes will be displayed on the right.

-- Under "Data series in:" click on   Columns
-- Enter that you want to use the first  1   column for X data
-- Enter that you want to use the first  0   row for legend text
-- Click on   Next>

• Define the chart title and label the axes.

In the Step 5 window,

-- Click on   Yes   under "Add a legend." (We will later change it from "Series 1.")
-- Title the chart

TXY DIAGRAM FOR BENZENE/TOLUENE AT 760 MM HG

-- Title the Category(X) axis Benzene fraction
-- Title the Value(Y) axis Temperature (Deg. C)
-- Click on Finish.
-- Save.

The graph will appear in the specified location on the worksheet. If necessary, scroll down so that you can see the entire graph window.

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

2. Modify the appearance of the graph.

• Specify whether you want gridlines displayed. We don't in this example, but let's see how you would get them if you wanted them. First, double-click on the graph window to activate (select) the graph. You should see two sets of 8 dots, at or near the corners of the graph window and at or near the midpoints of the window borders. If you don't see them, keep clicking until you do.

Under the   Insert   menu, select   Gridlines. If you clicked in the appropriate boxes on the dialogue window that comes up, you could get vertical or horizontal gridlines at major and minor tickmarks on the graph. (Don't do it now.) Click   Cancel.

• Rescale and format the axes. Put the pointer directly on the horizontal (x) axis and 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.
-- Under "Font," select "Font style:" = Regular.
-- Under "Number," select Category = Number, Decimal places = <3>.
-- Under "Alignment," select the middle vertical option (text going from bottom to top)
-- Click on   OK.

Repeat this procedure for the vertical axis. Double-click 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.
-- Under "Font" select Arial, regular.
-- Under "Number" select Number, 0 decimal places.
-- Under "Alignment," designate the horizontal text position.
-- Click on   OK.
-- Save.

• Rename the legend. Activate the chart (click on the graph window to bring up the sizing dots), point directly at the curve, and double-click. You should see a series of squares along the length of the curve. If you don't see them, single-click on the curve to bring them up.

Still pointing directly at the curve, double-click. A "Format Data Series" window should come up. Under the "Name and Values" tab in the "Name:" entry box, change "Series 1" to   Liquid: T vs. x . Click on   OK.

• Remove the legend border and change the legend background color to white. 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 white (lower right box).
-- Click on OK.
-- Save.

3. Add the second plot (T vs. y) to the graph. The easiest way to add a second plot to a graph is to put the new data point coordinates in adjacent columns (horizontal axis coordinates on the left), copy the 2-column range, and paste it into the graph.

• Copy the T column and paste it to the right of the y column. Here is one way to do it.

-- Select the T column including the heading (Press and hold on Cell [B10], drag down to Cell [B22], and release).
-- Move the pointer to the right edge of the highlighted area. The pointer should become an arrow.
-- Press and hold the right mouse button, drag the area to Column G (Row 10 through Row 22), and release.
-- Save.

• Paste the data points on the new plot (horizontal axis values in Cells F12:F22, vertical axis values in Cells G12:G22) into the graph.

-- Highlight the rectangular range [F12:G22].
-- Move the pointer to the right edge of the higlighted area. The pointer should become an arrow.
-- Press and hold the right mouse button, drag the area into the graph, and release.

A "Paste Special" window will appear. Place check marks as follows:

-- Add cells as New Series
-- Y values in Columns
-- Categories (X values) in first column
-- Click on OK. The second plot will now appear in the graph.
-- Save.

• Change the line style and rename the legend of the second plot.

Currently the two plots are shown as two solid lines of different colors. We want to change the "Vapor" line to be a dashed black line. (Different colors cannot be distinguished on a black-and-white printout.) We also want to change the legend to "Vapor: T vs. y."

-- Activate the graph by double-clicking on it.
-- Click on the top (vapor) curve on the graph to bring up the boxes along the length of the curve.
-- Still pointing directly at the curve, double-click to bring up the "Format Data Series" window.
-- Under the "Name and Values" tab in the "Name:" entry box, change "Series 2" to   Vapor: T vs. y .
-- Under the "Patterns" tab, "Line" options, click on the arrow to the right of "Style:" and select a dashed line.
-- Still under the "Patterns" tab, "Line" options, click on the arrow to the right of "Color:" and select black.
-- Click on OK.
-- Save.

The graph should now look like the one at the beginning of this tutorial.

• Move the legend inside the plot area. Press and hold on the legend, drag it to the upper right of the plot area, and release.

• 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 gray area of the graph to bring up the "Format Plot Area" window.
-- Under the "Area" section, click on the white box. Click on OK.

• Resize the graph. The axis labels are rather crowded. Adjust the window size until you are satisfied with the appearance of the graph.

• Save.

4. Print the worksheet. Deselect the graph (click anywhere on the worksheet outside the graph window). Choose the File menu and select Print.

The worksheet and the graph should come out on your default printer. If you wish to print only the graph, click on the graph and then print. EXCEL will automatically scale up your graph to fit on one full page.

5. Exit from EXCEL. Select Exit under the File menu.
A description follows of the vapor-liquid equilibrium problem that this spreadsheet is designed to solve. If you don't wish to read it now, you may exit from the tutorial.

APPENDIX: PHYSICAL BASIS OF THE TUTORIAL PROBLEM

The vapor pressure of a substance, p* , can be expressed as a function of temperature, T, 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 (mm Hg):

 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*(100oC) = 103.1305 = 1350 mm Hg

Another way of stating this result is that at a pressure of 1350.5 mm Hg, the boiling point of pure liquid benzene is 100°C.

Suppose we have a liquid mixture of benzene and toluene, with x being the fraction of benzene (technically x is the mole fraction of benzene in the liquid mixture, but if this means nothing to you it doesn't matter). That is, if x=0.30, the mixture contains 30% benzene and 70% toluene. Further suppose that the mixture is heated in an open flask on a day when the barometric pressure is Patm = 760 mm Hg. (Under normal atmospheric conditions, Patm varies in the range 750-770 mm Hg.) As the liquid temperature increases, the vapor pressure of benzene (pa*) and that of toluene (pb*) both increase, following the Antoine equation. 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 (pa*) and (pb*) 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 Patm (=760 mm Hg), 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 trial-and error calculation outlined at the beginning of the tutorial.

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

2. Calculate log10pa* and log10pb* from the Antoine equation. Then determine pa* and pb* by raising 10 to each of the two calculated values.

3. Calculate pa and pb from Eqs. (8) and (9).

4. Calculate (pa + pb) 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.

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

If 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. For example, if we heat a 50% liquid mixture of benzene and toluene (x=0.50) at a pressure of 1 atm (760 mm Hg), 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).