Formulas

Further Adventures in Excel

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.

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

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

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.

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.