View Stats
I’ll
Give You a Definite Maybe
A Introductory Handbook on Probability,
Statistics, and Excel
[This text, which has been prepared by Ian Johnston of Malaspina University-College in Nanaimo, for students in Liberal Studies, is in the public domain, released May 2000, and may be used by anyone, in whole or in part, without charge and without permission. This text was prepared in May 2000; minor editorial and formatting changes were made in November 2004]
For comments, questions, corrections, improvements, and what not, please contact Ian Johnston
Section Two: Introduction to Excel
A. Introductory Note
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 an earlier version of Excel. Most of the functions are unchanged in Excel 2000. Where the difference might be confusing, a special comment refers to that difference in Excel 2000.
B. Preliminary Steps in Excel
Starting Up
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 (the buttons included may vary considerably from one machine to another, depending upon how the program options have been selected, but that is not a problem). At the bottom of the screen there is horizontal row of rectangles labeled 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 and the Cells
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.
Selecting a Cell
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.
Selecting a Range (Group) of Cells
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 darkened, 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.
Selecting an Entire Row or an Entire Column
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 gray area). Click the left mouse button on the letter or number. Notice that the entire row or column turns dark.
Entering Data in a Selected Cell
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.
C. Exercise on Entering Data and Formulas on the Worksheet
Entering Numbers
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.
Mathematical Function Keys 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 that there are no spaces between any of the parts of such a formula.
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 (make sure you put in the equal sign). Do not put any spaces in between the numbers or the mathematical symbols. 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
Before moving on, delete the numbers in cells C1 and C2. Leave the data in cells A1 to A11.
Important Procedure
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.
D. Inserting a Formula into a Cell
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).
E. Some Short Cuts
Excel contains a number of short cuts to make calculations even faster. We will be going through a few of 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 (the first cell, followed by a colon, followed by the last cell, with no spaces). 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.
F. Expressing Scores out of Different Totals
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. You should still have in Column A the marks you entered as scores for the exercise in Section C above (if you have erased them, then enter them again in Column A). 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). The decimal .25, of course represents the fraction of the mark out of 100 which will make the mark out of a total of 25.
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.
Copying a Formula for a Range of Cells
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.
However, once you have entered a formula into a cell, Excel provides a convenient shortcut for copying that formula into neighbouring cells without having to use the top menu (the procedure we have just reviewed).
First select the cell with the formula in it. Notice that when you select that cell, there is a border around the cell with a small rectangle in the bottom right hand corner. If you position the mouse pointer directly over that small rectangle, the pointer will turn into a cross hair. Press down the left mouse button and hold it down, dragging the mouse through the cells into which you wish to copy the formula. When you reach the last cell to receive the formula, let go of the left mouse button, and select a cell outside the range. You will notice that whatever is in the original cell with the information is now in each of the cells you dragged the mouse pointer through.
G. Two Formatting Options
In Excel you can alter the width of the columns manually. Simply position the mouse cursor directly over the line between the two letters identifying the column (e.g., between B and C). The cursor will turn into a double arrow. If you hold down the left mouse button and move to the left or right, you will see the width of the column change. This option is useful when the cell contains more information than it can display.
You can also add or delete rows or columns. 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 re-label 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. Remember that if you instantly regret a deletion you can restore the lost material by selecting Undo from the Edit drop-down menu (or clicking on the Undo button on the Toolbar, the button with the looping arrow pointing to the left).
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.
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]