I’ll Give You a Definite Maybe
[A
Handbook on Probability, Statistics, and Excel by Ian Johnston of Malaspina
University-College. This text is in the
public domain, released May 2000]
Section Two: Introduction to
Excel
This section introduces students to Excel, a very
powerful, popular, and sophisticated spreadsheet program (the meaning of this
word will become apparent). Providing
students a basic familiarity with Excel is the major purpose of the
module. The concern here is to help students
begin using the program, especially its ability to analyze statistics and to
produce charts, while at the same time encouraging them to develop their
knowledge of probability and statistics.
This module only touches on a very small part of
what Excel can do and does not go through all the various short cuts. However, there should be enough here to
allow students who are interested in learning more about this popular program
to continue on their own.
Excel has a number of self-help and tutorial options
which students can use if they want to take extra time to learn more about the
features of this software. The
assumption in these pages is that the readers know nothing at all about Excel
and are undertaking the first steps in the program.
Note that these instructions refer to Excel 5.
Begin by selecting Excel from the programs listed
in Windows. When the Excel program
comes up on the screen, it will present a grid made up of many similar
rectangles, with some menu options at the top (File, Edit, View, Insert, Format, Tools, Data, Windows, Help).
Underneath the word File at the top left there are a number squares with
symbols on them; this part of the screen is called the Toolbar. At the bottom of
the screen there is horizontal row of rectangles labelled Sheet 1, Sheet 2, Sheet 3, and so on.
This entire set up is called a Worksheet. Notice that just above the top left corner of the grid, there is
a label: Sheet 1. Sheet 1 is the name of the worksheet you begin
working with. It will keep that name
until you change it to a name you have selected.
The grid is made up of columns, each one with an
alphabetical letter on the top. There
are 156 of these columns, from A to IV.
The horizontal rows have numbers from 1 to 16384. The intersections of the horizontal and
vertical lines create the grid. Each
rectangular space defined by these intersections is called a cell.
The full worksheet thus has a large number of cells (about 2.5 million). In our exercises we will be using relatively
few of these cells in the top left corner.
A cell is defined or named by the column letter
and row number which locate it in the grid: e.g., the top left cell is A1; the
second vertical row of cells is B1, B2, B3, B4, and so on. The vertical ranks of cells are called columns; the horizontal ranks of cells
are called rows. Cells in the same column thus all have the
same letter (A, B, C, D, and so on) and different numbers; cells in the same
row all have the same number, but with different letters.
When you first call up the Excel grid, you are
presented with a blank Worksheet. You
will notice that the worksheet is empty and that cell A1 is highlighted, with a
dark line around its perimeter. This
dark perimeter means that cell A1 has been selected to receive some data.
You can select any cell in the grid simply by
moving the mouse symbol (the intersecting cross-shaped rectangles) to that cell
and clicking the left-hand mouse button.
If you carry out this procedure, you will notice that cell A1 is not
selected any more (the dark perimeter disappears) and that the cell you pointed
to now has the highlighted border around it.
If you now move the mouse and click on another cell, that one will be
selected in place of the previous one.
Try moving the mouse pointer around to select different cells, until you
are quite familiar with this procedure.
You can also move from one cell to another with
the move arrows on the keyboard. As you
press the arrow key, the selected cell moves one cell in the relevant direction
(up, down, left, or right). In every
case you can tell which cell has been selected by the dark border around
it. This method of getting around the
grid (by using the arrow keys) is convenient when you wish to move only one or
two cells from the selected cell. Using
the mouse to select a cell is, as we shall see, appropriate in a number of
other procedures.
It is possible to select more than one cell at a
time (a very common procedure). For instance, if you want to select cells A1,
A2, A3, and A4 (the first four cells in Column A), move the mouse pointer to
cell A1, push down the left mouse button and hold it down. Then drag the mouse (still holding the left
mouse button down) from cell A1 straight down to cell A4. Release the left mouse button when you have
selected the range of cells you want.
Notice that when you do this the first cell (A1) is white, and the other
selected cells are black, with a highlight around all the selected cells. If you make a mistake, select a cell outside
the range, and then start again to select the range you want.
You can select cells horizontally as well by the
same method (e.g., A2, B2, C2, D2, E2, and so on). Once again, notice that all the selected cells are black, except
the first one (where you started). In
this way you can also select blocks of cells in adjacent columns and rows. For instance, if you start in cell A1, you
can drag the mouse down to A4 to select cells A1, A2, A3, and A4. If you then continue to drag the mouse
horizontally (without releasing the left mouse button), you notice that the
range of selected cells now includes B1, B2, B3, and B4. In all cases, the selected cells are black,
except for the first one, which is white with a border around it. Try these moves until you are quite familiar
with selecting a series or a range of cells.
If you make a mistake and wish to select a range
of cells again or to deselect a range of cells, simply move the mouse pointer
to an unselected cell and click the left mouse button. This procedure will change the selected cell
or cells to the one you have just selected.
Make sure you are quite familiar with this process
of selecting a range of cells; we are going to use this procedure repeatedly.
Sometimes you may wish to select an entire row or
an entire column. This procedure is
necessary if you wish to add or delete a column or if you wish to change all
the data in the column or row (for example, by putting all the entries in bold
or by adding to or subtracting from the number of decimal points).
To select a column or row move the mouse arrow to
the alphabetical letter or the number which identifies that row or column (at
the top or on the left of the worksheet—the letter or number in the grey
area). Click the left mouse button on
the letter or number. Notice that the
entire row or column turns black.
To delete a column or row, simply select it (using
the procedure mentioned above), go to the Edit menu, click on the word delete. That column or row will disappear, together
with all the data in it. Excel will
automatically relabel all the columns or rows.
Notice that you will lose all the data in the deleted row or column, and
if any formulas in other cells depend on the information in that column, those
formulas will be affected.
To insert a column or row, simple select the
entire column or row, click on the Insert label at the top, and from the
drop-down menu, select Row or Column.
The new row will appear immediately above the row you selected; the new
column will appear immediately to the right of the column you selected.
Note that inserting new rows and columns does not
affect any formulas you have entered in Excel, because the program
automatically changes the entries to accommodate the additional column.
Try inserting and deleting columns and rows, until
you are familiar with the procedure.
Once a cell has been selected, you can enter
information in it. In most procedures,
you will enter one of three kinds of data: (a) text (especially for headings),
(b) numbers, and (c) formulas.
For example, select cell A1. Then, using the keyboard in the normal
fashion, type the heading “Scores.” As
you type, you will notice that the word “Scores” appears in cell A1 and on a
line just under the toolbar (in a space called the Formula Bar). If you make a
mistake (e.g., spelling) and wish to correct it, edit just as you would in a
word-processing program (with the delete button and the arrow keys), following
what you are doing on the Formula Bar just below the Toolbar.
When you have entered the word “Scores,” strike
the Enter key. Notice that the selected
cell now moves to A2 and the text you entered is in cell A1. If you want to change the text in cell A1,
select the cell (by moving the mouse indicator into cell A1 and clicking the
left mouse button or else by using the arrow keys to move the highlight to cell
A1) and press the backspace button. The
contents of cell A1 will disappear, and you can now write in the heading you
want.
Note this procedure for deleting the contents of a
cell. First, you select the cell. Then, you press the backspace key. The contents of the cell will disappear. Actually, you do not even need to press the
backspace key. If you select a cell
with something in it, as soon as you start to enter new text or data, the
material previously in the cell will disappear.
You enter numbers into a selected cell in the same
way: first, select the cell; second, enter the numbers you wish; third, move on
by pressing Enter (or selecting a new cell).
Once again, the digits you entered will remain where you put them.
The worksheet in front of you should be blank
except for a heading in cell A1 titled “Scores.” If it is blank (i.e., if you are starting anew here), select cell
A1, and then type in the heading “Scores.”
Then in column A (starting in cell A2) enter the following ten numbers
in order:
A2: 77
A3: 65
A4: 45
A5: 90
A6: 73
A7: 54
A8: 88
A9: 64
A10: 72
A11: 80
Suppose these numbers indicate the percentage
results on a class test. We want to
analyze these results in order to compare them with the results on the same
test in a number of other classes (we are going to be doing a lot of exercises
similar to this).
Now there are a number of ways we can provide a
description of this list of results.
The most common is to calculate the class average (the mean) of all the scores. Excel will do this for us very quickly, but
first we have to learn the symbols for mathematical operations in Excel.
The keyboard symbols for mathematical operations
in Excel are as follows (locate these keys on the keyboard):
add: +
subtract: -
divide: /
multiply: *
To indicate a mathematical operation, simply
indicate the number, the symbol for the operation, and the second number
(without any spaces). Thus, 8*4 tells
Excel to multiply 8 by 4; 56.7/3.2 tells Excel to divide 56.7 by 3.2; A6*.2
tells Excel to multiply the value in A6 by .2, and so on.
Note carefully that in multiple operations Excel
performs calculations in the usual mathematical way: first, it completes all operations
in brackets, then it multiplies and divides, and finally it adds and
subtracts. In other words, it does not
necessarily complete operations in the sequence from left to right.
So, for example, the calculation 2+4*5 produces
the result 22. The multiplication is
done first (4*5 = 20), and then the addition (2 + 20 = 22). If we went strictly from left to right, we
would do the addition first (2 + 4 = 6) and then the multiplication (6*5 = 30). This latter procedure would be incorrect.
If we wanted the addition performed first, then we
put the addition in brackets, as follows: (2 + 4)*5. This procedure would produce a result of 30.
Try this out in Excel. Select cell C1. In it
type the following: =2+4*5. Do not put any spaces in between the numbers
or the mathematical symbols (and remember to start with the = sign). Then strike Enter. Notice that the result which appears in cell C1 is 22.
Now select cell C2, and type in the following: =(2+4)*5, remembering again to start
with the equal sign. Then strike
Enter. Notice that in this case the
result is 30.
Whenever you enter a formula into a cell, you must begin with the equal sign (=) before the details of the mathematical formula. Otherwise Excel will simply put into the cell what you have typed. The equal sign tells Excel to perform the calculation and not just to put the figures you have typed into the cell.
Before moving on, delete the numbers in cells C1
and C2. Leave the data in cells A1 to
A11.
A formula in a cell indicates that you want Excel
to carry out a calculation of some sort and to enter the results into a
particular cell. Learning to use
formulas will enable you to use Excel to carry out all sorts of complex
mathematical functions quickly.
For instance, let us go back to the list of ten
percentage scores you entered in cells A2 to A11. If we want to know the average mark, then we need to add up the
figures and then divide the total by the number of entries in the list
(10). Excel will do this rapidly.
Select cell A13.
In this cell we want the total of all the numbers in the list. We can ask Excel to do this in a
formula. To insert the formula, type in
cell A13 the following formula (note that there are no spaces anywhere in the
formula):
=Sum(A2+A3+A4+A5+A6+A7+A8+A9+A10+A11)
The strike Enter.
The total (708) will appear in cell A13. The formula has told Excel to add up a range of cells and has
indicated each cell in the series to be added up. If you get an error message in the cell, review carefully the
formula you have entered. The slightest
error in the formula (e.g., a space between two elements or a missing final
bracket) will cause Excel to indicate an error.
Since cell A13 contains a formula, it will process
whatever numbers appear in the named cells (A2 to A11). If one of those number changes, the total in
cell A13 will change accordingly. Try,
for example, altering the number in cell A2 from 77 to 98 and striking the
Enter key. Observe what happens to the
total in cell A13 as you do so.
If we want the average of the percentage scores,
we have to divide the total we have just created with the formula by the number
of entries in the list (10). We can do
this with a second formula. So select
cell A14, and in that cell write the following formula:
=A13/10
This formula is asking Excel to take the total in
cell A13, divide it by 10, and enter the result in cell A14. If you press Enter, you will notice that the
appropriate figure appears in A14.
As before, if any numbers in the original list
change, then the values in A13 and A14 will change accordingly. Try, for example, changing the number in
cell A2 from 98 back to 77. Observe
what happens to the numbers in cell A13 and in cell A14. Since the numbers in these cells are
produced by formulas, they will change if the numbers which make up the
formulas change.
Formulas in Excel can be quite long, and in
calculating the average of the list of numbers we could have combined both
steps in a single formula, as follows:
=Sum(A2+A3+A4+A5+A6+A7+A8+A9+A10+A11)/10
This formula is telling Excel to add up the
numbers from A2 to A11 and to divide the total by 10. If you select cell A15, enter this formula, and strike Enter, you
should get exactly the same result as the number in cell A14 (which was
produced by doing two formula operations).
Excel contains a number of short cuts to make
calculations even faster. We will be
going through these as various times, but here are a couple to introduce you to
the convenience of Excel.
In the above process of entering a formula for the
total of the ten cells, we had to enter each cell individually (e.g., A1+A2+A3
and so on). This is time consuming and
unnecessary, because you can use a shortcut which indicates all the cells in
the range you want to add up. The
formula for the short cut is A2:A11.
This symbol means all the cells between A2 and A11 inclusive. Thus, instead of the cumbersome formula in
which each cell had to be named we can now write
=Sum(A2:A11)
Similarly, Excel will automatically carry out
certain calculations simply with a word command. We will be encountering many of these later on. For the moment, however, notice that if you
want the average of a list of numbers, you can simply ask Excel to give you the
average. If, for example, we want the
average of our 10 numbers from A2 to A11, we select a cell in which we want the
average to appear, and then type in the following formula (notice that there
are no spaces anywhere in the formula):
=Average(A2:A11)
This formula is asking Excel to provide the
arithmetical average for all the numbers between A2 and A11 inclusive and to
enter the result in the selected cell (where you have written the
formula). Pressing Enter will produce
the numerical average of the numbers entered in the cells A2 to A11 inclusive (1). This method is considerably shorter than the
ones we used above.
Excel has a large number of mathematical
operations which it can carry out in this way (i.e., with a verbal command and
a cell or cell range designation). For
example if you wish to know the square root of a number in a cell, select an
adjacent cell and enter the following formula (note that there are no spaces
anywhere in the formula):
=SQRT(cell number)
If you ask Excel to perform an operation on a cell
which is empty, you will normally get an error message (usually preceded by the
symbol #). Similarly if you ask Excel
to perform the operation on the cell in which you are entering the formula, you
will get an Error Message. The
immediate response to any error message should be to check very carefully the
formula you have entered.
If you get an error message you do not understand,
consult the Excel Help menu, under the Index option. The top of the list contains a number of common error symbols.
Let us return to our list of marks in column
A. These are all out of 100. Suppose now I want to express these marks
out of 25 rather than out of 100 (a common procedure in sorting out marks and
calculating final grades).
To convert any percentage result into a mark out
of another total I multiply that percentage number by the new total divided by
100. For example, if I want to know
what a percentage score of 68 is out of, say, 25 (i.e., what is 68 percent of 25)
I multiply 68 by 25/100 (or by .25).
Excel can do this for us. First put a heading in B1: Total (25). This will remind us that this column
expresses the results out of 25. Then
select cell B2. Here we want the mark
in A2 (out of 100) expressed as a mark out of 25. To instruct Excel to calculate this mark, enter in B2 the
following formula:
=A2*.25
This formula is telling Excel to multiply the
number in A2 by 0.25 and to enter the result in cell B2. If you enter the formula and strike Enter,
you will see the result. If there are
any problems, check very carefully the formula you entered (remember to include
the equal sign and not to have any spaces).
Notice this procedure for converting a percentage
mark into a total out of something less: we simply multiply the percentage
result by a decimal fraction. To get a
mark out of, say, 15, we would multiply by .15; to get a mark out of 5 we would
multiply by .05; and so on.
Suppose now we want to convert all the remaining
marks in the A column to totals out of 25 (as we just did with the mark in
A2). One way would be simply to repeat
the process, cell by cell, for B3, B4, B5, and so on down to B11.
There is a much quicker way to get the same
result. Once we have entered the
formula in cell B2 (as we did above), we can then copy that formula and apply
it to all the cells we are interested in.
To practice this procedure, carry out the following steps.
1. First select cell B2. It already has a formula in it, and we want to copy this formula and apply it to all the cells from B3 to B11. Having selected B2, move the mouse pointer to the Edit word at the top of the screen, click the left mouse button, and from the drop down menu which appears, select Copy by pointing to it and clicking the left mouse button. The menu will now disappear, and you will notice that cell B2 has a moving black and white highlight around it.
2. Now for the tricky part (at first). Put the mouse indicator in cell B2, press down the left mouse button and hold it down. Then drag the mouse pointer through cells B3, B4, B5, B6, B7, B8, B9, B10, and B11, just as you did earlier in selecting a group of cells. You will notice that the selected cells turn black, except that the original cell (B2) remains as before. Release the left mouse button when you have selected the range of cells you want. If you make a mistake, click on a random cell to clear the mistake, and start again.
3. When you have selected the cells for which you want the formula copied, strike the Enter key. You notice that the selected cells in the B column now contain the appropriate numbers, the values in column A, all multiplied by .25, so that the A values are now expressed as values out of 25 in the B column. Strike Enter again, and the black background in the range disappears (or you can select a cell outside the range. The formula will have been copied into the previously empty cells.
Make sure you understand these copying procedures
(or the short cut outlined below); we will be using them a good deal in the
exercises that follow.
Shortcut for Copying Formulas
Notes to Section Two
(1) Note that if there is a blank cell in the column of numbers being entered into the calculations, then Excel will not count that; however, if there is a 0 in the cell, then that will be counted. In other words, in the calculation of the average, only the cells which contain numbers are included. [Back to Text]
[Back to johnstonia Home Page]