Revised from the tutorial
by Prof. Joseph F. Lomax, United States Naval Academy.
Part II: Copy & Paste, Insert Row or Column, Formulas
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 on the F:\ drive. 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
EXERCISE 1: Block Copy
(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 - Colulmn.
(c) Highlight Row 1 by SELECTING the 1 label.
(d) From Menu Bar SELECT Insert - Row.
(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 - Columns - 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.
EXERCISE 3: Formulas
(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.
EXERCISE 4: Addresses
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/$Y$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.
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 $Y$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 Y 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.
(c) Now copy the formula in cell C2 into cells C3:C94. The
answers should range from 1.67E-24 to 3.94E-22.
EXERCISE 5: @ Functions
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 107 amu). Let's test this. In cell B95, type
@average(B2:B94) and hit the Enter key. You could accomplish
the same thing by editing the cell formula. Double-click on cell B94. You
will see @count(B2:B94). Either use your mouse to highlight count,
or back-arrow and then delete count. Now, type in avg into its
place and hit the Enter key. As you see below, you get a result which is
a little above 107. You may note that it also has a huge number of digits
showing. Even if you were to change the numeric format (do this by clicking
the right button on your mouse and then SELECT Format Cells - Number),
it would still keep all of these digits in the file.
(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 Part II: Copy & Paste, Insert Row
or Column, Formulas. You may want to save your file and exit Excel.
Return to the Excel Tutorial Homepage.
This page was last revised on 24 April 2003.
If you have any problems or questions about the program,
contact David Horner.
|