The names, ages and salaries for four people are entered in rows 3 through 6 of columns B, C and D of the first sheet in the blank Excel workbook. For readibility, the words Name, Age and Salary are entered in the second row. The word Average is entered in cell B7 (i.e., column B and row 7).
In cell C7 we want to have the average age of the four people. We could do this the hard way by using a pocket calculator to compute (38+26+42+28)/4. This yeilds 33.5. This, however, totally defeats the point of using a spreadsheet in the first place. Instead, we should enter the formula =AVERAGE( C3:C6 ) in cell C7. The equal sign tells Excel that you are entering a formula (as opposed numeric or character data); the word AVERAGE is a keyword that instructs Excel to compute the average of a set of numbers; and C3:C6 is a shorthand way of telling Excel to use the data in cells C3, C4, C5 and C6.
As soon as we press the Enter key, Excel computes the average and displays the result where we entered the formula. The formula is still there. If we modify any data (say, change Jack's age from 38 to 83), the formula will automatically re-compute the average of the four ages. Unlike the pocket calculator approach, we do not have to do any revised calculations.
Now we need to compute the average of th salaries in column D. We could do this by entering the formula =AVERAGE( D3:D6 ) in cell D7. It would take long to type this, but there is even a faster way to make copies of cells. Since the D7 formula is just like the C7 formula (except that it uses column D data instead of column C data), we can simply drag a copy of C7 into cell D7. This is done by moving the cursor to the lower right corner of cell C7. The cursor will change shape as it moves over the corner of the target cell. When this happen, press and hold the mouse button while you drap the cell over to the D7 destination. When you release the mouse button you will have the desired formula.
Home | Lectures
| Handouts | Assignments |
This website was originally developed by John Mote for his MIS 311F class. This site is
now maintained by the Department of Civil Engineering
at the University of Memphis. Your comments and questions are more than welcome.