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

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 (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

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.

      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

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

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

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

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

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

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

Plotting: Windows 3.1

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

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

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

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

17. Exit from EXCEL. Select Exit under the File menu.

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

and so

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 P_atm = 760 mm Hg. (Under normal atmospheric conditions, P_atm varies in the range 750-770 mm Hg.) As the liquid temperature increases, the vapor pressure of benzene (p_a*) and that of toluene (p_b*) 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 (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 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 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.

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