Spreadsheet Assignment 01
Basic Computing and Plotting Fraction of Species
Your task is to duplicate the spreadsheet above. Be careful to distinguish between values that have been entered by hand and those which have been calculated. You will not receive credit for this assignment if you laboriously copy each and every number shown onto the spreadsheet! (I have seen students do this. It sort of defeats the purpose of using a computer, doesn't it?) Here is how it was done: The information in rows 1 through 13 is just text typed into the column A cell. Such text is ignored by the computer but is useful to the humans reading the spreadsheet. In cell A3 type Your Name and the current Date In rows 15 through 19 we have a parameter block. Labels are put in column A, and the corresponding numeric values are put in column B. Note the use of the computer version of Scientific Notation used for the K values. Excel does not support superscript and subscript in any useful fashion, so we have to write formulas in a way that is mostly readable. becomes CofH2S, for example. In row 21 we have labels for each quantity we wish to calculate. In cell A23 we put our initial value of pH, in this case 0.
In cell B23 put the formula =10^-A23. This computes [H+] from pH. Fill this down to cell B51. In cell C23 put the formula =B23*B23+$B$17*B23+$B$17*$B$18. Note the relative references such as B23, which point to the cell to the left of the formula which contains the [H+] it needs. This reference will change as this formula is filled down. $B$17, on the other hand, is an Absolute Reference and always points to the cell that contains the value of K1 needed in the calculation. It is left as an exercise for the student to devise the formulas to put in the cells that generate alpha0, alpha1 and alpha2. Format the numbers you have generated so they appear as shown. pH uses the 0.0 format, and all the others use the 0.000E+00 format. Select the numbers to be formated, then choose Format Number from the appropriate menu. (Excel 5.0 has hidden this in dialog boxes found by choosing Format Cells... from the Format menu.) Now that all of the values have been generated, it is time to produce the graph. This is far easier to show on an actual compute than it is to give detailed written instructions, so don't hesitate to ask for help on this one. Here are a few hints, however. Although the data are evenly spaced, I still want you to use a Scatterplot when producing this graph. Your X values will be in column A, and the three Y values to be plotted will be in columns D, E, and F. This introduces a few complications. You will probably need help getting this all to plot on your own computer, so don't hesitate to ask if you get stuck. Here is how it worked in Microsoft Excel 5.0 on a Macintosh.
|