Basic Operations

Chapter Contents

This book is about problem solving so we shall spend little time on the preparation of presentation-worthy worksheets. We will give some information on how to make a worksheet more readable, but the emphasis is on mathematical operations. The topics in this chapter include

 entering numbers, including fractions and percentages;

 simple formulas such as = A1+B1+C1;

 range finders (colored borders showing what cells are used in a formula);

 arithmetic operators +, −, * , /, and ˆ;

 the Evaluate Formula tool;

 error values such a #DIV/0! and #VALUE!;

 copying with commands and shortcuts;

 formatting numbers;

 the difference between stored and displayed values;

 round-off errors resulting from the IEEE 754 standard.

If you are familiar with an earlier version of Microsoft Excel, you may be tempted to skip this chapter. You are urged to at least read the exercises to find out about new Excel 2013 features.

Exercise 1: Simple Arithmetic

Imagine that from time to time, you are given some data consisting of rows of three numbers and you are asked to find the sum and product of each triple. Of course, this could be done with a simple calculator, but a spreadsheet offers three advantages: we can reuse our spreadsheet from day to day, we can see the values we have entered, and we can make a neat printout of the results. Our completed spreadsheet will look like Figure 2.1:

a. In cells A1 to E1, enter the text shown in Figure 2.1. In A2:C3, enter the numbers shown. You will note that as you enter the text, it is left aligned in a cell, while numbers are right aligned.

b. Use the mouse to select A1:E1. On the Home tab, click the right alignment command in the Alignment group; it is the third command in the second row of this group.

c. Unless we have used a spreadsheet before, we might be tempted to type = 1+3+4 in cell D2. Try this (remembering to press when finished) and it will give the correct answer, but this totally ignores the main idea behind a worksheet. We should not have to retype data. Wherever possible, formulas should refer to cell values. Click on D2 and tap the key to remove this formula.

d. Now type = A2+B2+C2 and click the check mark to the left of the formula bar when the formula is complete. Notice that, as you type, the status bar displays ENTER, but once the checkmark tool to the right of the formula bar (or the key) is used to commit the formula, it shows READY.

e. Next, we see another way to build a formula. In D3, type an equals sign (=), but rather than typing A3, click the A3 cell. Now type + and continue building the formula with this pointing method. The status will alternate between POINT and ENTER. Note how the cells take on a colored border that matches the colors of the cell references in the formula (Figure 2.2). Again, click the checkmark to commit the formula once it is finished. In this case, pointing has little advantage over typing, but in other cases, it has some advantages. Pointing helps to ensure we reference the correct cell in a complex worksheet, and it is very useful to reference a cell on another sheet that could be in another workbook.

Of course, we do not have to rebuild the formula for every cell. We can copy from one cell to another. Here, we look at two ways of doing this, and a little later, we will see a third (and the fastest) method.

f. The first method uses the Copy and Paste commands located on the Clipboard group of the Home tab (far left)—see Figure 2.3. With D3 as the active cell, click the Copy command. Select D4:D5 and use the Paste command (this is the larger icon on the group). Note how the cell we copied (D3) has a mobile dotted border. While this “ant track” is visible, the contents of the cell are still on the Clipboard and may be copied to any other range or cell. The ant track disappears as soon as you start to edit any cell but can also be removed by pressing .

If you allow the mouse pointer to hover over the commands in the Clipboard group, screen tips pop up to tell the purpose and the shortcut keystrokes for each command. So Copy is + C and Paste is + V.

g. Delete D3:D5 and repeat the copy-and-paste action using the + C and + V shortcuts. If you accidentally delete D2, use + Z to undo the action.

h. Delete D3:D5 again in preparation for another way to copy D2 down to D5. Move to D2 and note that the active cell border has a small solid square in the lower right corner; this is the fill handle. Carefully move the mouse pointer until it is over the fill handle—the pointer changes from an open cross to a solid cross. Hold down the left mouse button and drag the solid cross down to D5. In step (k) below, we shall see yet another method of filling a range.

For the final stage in this exercise, we look at another approach to building formulas. Rather than typing the formula in the cell, we will type it in the formula bar. There is an advantage to doing this when the formula is long, but we shall do it here for demonstration purposes:

i. Make E2 the active cell. In the formula bar, type =. Now complete the formula to be = A2*B2*C2 either by typing or by pointing. Commit the formula with either or the checkmark on the formula bar. Note that the multiplication operator is an asterisk (*).

j.  Lastly, we will fill in cells E3:E5. With E2 as the active cell, move the mouse pointer over the fill handle (watch for the change from open to solid cross) and double-click the fill handle. The formula from E2 is copied down to E5. This Auto Fill feature can be used with vertical tables (data arranged in columns) but not with horizontal tables. It can be used to renew formulas when you make a change to the top cell.

k. Double-click on any cell in the range D2:E5. Note that the status bar displays EDIT. But more importantly, observe the colored borders around the cells in the corresponding cells in columns A, B, and C. Excel uses these range finders to pictorially show you which cells a formula refers to.

l. In A6:C6, type some numbers. When you complete the last entry, Excel will automatically add the formulas in D6 and E6. This is an example of Auto Extend. It requires that the table has at least four rows of entries above the current row. For more information on this topic, see http://support.microsoft.com/kb/231002.

m. In the QAT,...