By Joseph F. Lomax with the help of ENS Lora Anderson, revised from the tutorial by Wayne Pearson and
John Schultz: all of the Chemistry Department, USNA.
Part II: Further Adventures in Excel 2000
If you do not already have the file, Atm_mass.xls, download it by clicking
here . A window
like the one below will show up. You can launch Excel and open the file by
SELECTING the radio button for Open it. When you do this, it will put
the file in a temporary directory (most likely C:\Windows\temp\). You may wish
to perform a SAVE AS command in Excel and save the file in a more permanent
directory. Note: If you wish to print out this Web file, make sure you format
your printer to print in landscape mode!!!
EXERCISE 1: Block Copy
EXERCISE 2: Inserting a Row and a Column
EXERCISE 3: Formulas
EXERCISE 4: Addresses
EXERCISE 5: @ Functions
(a) If it is not already up, retrieve the file atm_mass.xls from the
subdirectory you placed it in. If you find it and double-click on the file icon
(the yellow circled item in the image below), it will launch Excel for you. The
file appears to be empty but the data is in columns Y and Z.
(b) Move to cell Z1. Move by SELECTING the scroll bar on the bottom right
(blue circled item). You will notice the column label on the top of the
spreadsheet window (red circled item) 
Let's copy the data into another part of the spreadsheet. There are many
methods to copy a block in Excel. The most straight-forward is a Copy
and Paste.
(c) A whole column can be copied at once. Highlight column Z by SELECTING
the Z label for the column.
(d) Hit Ctrl-C or the Copy icon on the button bar. The Copy icon looks
like two overlapping pages (see the left icon in the green circle below).
(e) Highlight column A by SELECTING the A label for the column.
(f) Hit Ctrl-V or the Paste icon on the button bar. The Paste icon looks
like a clipboard with a piece of paper (see the right icon in the green circle
below).
(g) Note that the entire Z1:Z93 block of data is still there.
EXERCISE 2: Inserting a Row and a Column
We would like to place a label on the top of the data that is now in Column
A. We would also like to place the atomic number next to the atomic masses. If
we add a column we can place the atomic numbers in the new Column A. If we add
a row, we can place the labels in the Row 1.
(a) Highlight Column A.
(b) From Menu Bar SELECT Insert - Colulmns.
(c) Highlight Row 1 by SELECTING the 1 label.
(d) From Menu Bar SELECT Insert - Rows.
(e) Enter the labels Atomic Number into A1 and Atomic Mass
into B1.
(f) Atomic Mass in cell B1 overlaps Atomic Number in A1. Highlight cells
A1:B1. SELECT Format - Column - Autofit Selection. It will size the columns
to fit the size of the labels or numeric
format in the cells. The final result will look something like the image below
with cell B1 still highlighted. 
(a) Place the number 1 into cell A2.
(b) Place the formula =A2+1 into cell A3. You might wonder why you have
to have a leading = sign for the formula =A2+1. When
you enter the formula in, the leading the = indicates that the result is a value.
Thus, =A2+1 is a formula that results in a value whereas A2+1
(with no leading =) is a label. A number alone is always a
value, so they need no leading =.
(c) The result of the formula should be the value 2.
We are going to copy the cell A3 to the range A4:A94. Hopefully, the
utility of this action will become apparent.
(d) Highlight A3.
(e) SELECT Edit from the Menu Bar (green circled below). SELECT
Copy.
(f) Highlight the block A4:A94.
(g) SELECT Edit from the Menu Bar. SELECT Paste. The result should look somewhat
like the image below.
You will notice that the values in A4:A94 increase by one as you go
down from cell to cell. Look to the Editing line (to the right of the cell
address and hand). Now arrow down through the cells. You will notice that the
formula is changing. As you move to increasing row number, the row number in
the formula increments up. This is the beauty of spreadsheets. A simple
repetitive computation can be done in a snap.
For the next task, compute the mass of a single atom of each element. As
with any problem you must first decide how to solve the problem then apply the
power of the spreadsheet. You should be able to work this but here is the answer
just in case. The mass of a single atom of an element is simply the atomic mass
divided by Avogadro's number. For your convenience, Avogadro's number should
be found in cell Y2.
Let's put the results into column C.
(a) Place the label Mass Of An Atom into cell C1.
(b) Place the formula =B2/$Z$2 into cell C2 (see image below). Note: when
you enter the Formula into the Cell, the Cell displays the value corresponding
to the operation (1.67E-24) while the text line above, still shows the Formula.
(c) Your value may not read "1.67E-24"; it may read "1.67409E-24" or it may read "0".
This is because the format for the display of the number is set to some default. To
get your format to match the figure below, select the column and right-click.
Go to the "Number Tab" and SELECT Scientific. Now fill in the number of
places after the decimal, in this case 2.
The cell B2 is referred to as a Relative Address. It will change like
the addresses in column A. It is useful when you are using a series of numbers
that will have repetitious calculations done on them. The $ notation in $Z$2
indicates an absolute address. In all of the calculations we will want to use
the same value of Avagadro's Number. We do not want to have to put in 94 copies
of this number, so we specify that the address does not increment as we copy it
down to other cells. Both the Column Z and the Row 2 are specified (though in
this particular case, only the row need have been specified). You will use an
absolute address for any constant value that you need.
(d) Now copy the formula in cell C2 into cells C3:C94. The answers should
range from 1.67E-24 to 3.94E-22.
We are going to perform three statistical functions on this data. One
will give us an answer we expect, one will give us an answer we could estimate
and one will give us an answer we could not easily calculate.
Excel has a large number of canned programs. These programs also
start with the symbol =. If, after an = symbol, the program recognizes
text that is not part of a cell address, it will check to see if the text corresponds
to a canned program. Some perform mathematical
functions such as sin, cos, log and natural log. Others perform sophisticated
financial functions such as compound interest and mortgage principle and
interest. The ones we will be using are relatively simple statistical functions
of count, average and sum.
(a) In grade school a common exercise is to count the number of pages in a
book. Most of the 4th graders wildly count the pages one-by-one. A few know
the trick and look at the last page of the book and read off the number. We
know to look at the last value in the list, but we are going to allow Excel to be
an incredibly efficient 4th grader and count each element, one-by-one. Move to
cell A95. Type into the cell @count(A2:A94) and hit the Enter key.
You should get the value 93 showing in the cell (see below). If you
want to see how it works in other columns, you could copy that cell into
B95..D95. Again, you would find 93 in cells B95 and C95, but you would
find 0 in D95.
(b) If you took the average (mean) of the atomic masses, you would expect it
to be a value around or a little above the mean of the masses of elements 46 and
47 (about 112 amu). Let's test this. In cell B95, type @average(B2:B94)
and hit the Enter key.
(c) The final task in this set of Exercises will be to find the sum of the
masses of one atom each from every element. Though one might question the
ultimate reason behind this task, it is certainly impractical with a calculator
and a breeze with a spreadsheet. Corresponding to the change in part (b), type
@sum(C2:C94) and hit the Enter key or edit @count(B2:B94) to
@sum(C2:C94) and hit the Enter key. You should get something that
looks like the image below. 
This is the end of this Further Adventures in Excel. You may want
to save your file and exit Excel.
Return to the
Excel 2000.
This page was last revised on 21 July 1999.
|
