Adams Toolkit - Spreadsheets

One of the most powerful capabilities of spreadsheets is their ability to function like automated calculators. This capability is possible through the use of cell reference formulas. In its simplest form, you can use your spreadsheet to add, subtract, multiply, or divide sets of numbers.

The images which follow provide an example of using formulas to calculate total games played, winning percentage, and total wins. Also included are images which show an example of how to format how numeric results are displayed by the spreadsheet.

Entering a cell formula is similar to typing in an equation on a calculator. The difference is that cell references (or cell addresses) are used in place of numbers. for basic math operations, use symbols as follows:

+ for addition
- for subtraction
* for multiplication
/ for division

To enter a cell formula, start by pressing the equal (=) key on the keyboard. (The equal sign is the signal to Excel that you are entering a formula instead of text.)

After the equal sign, you can highlight or type the cell address of the first item to be used in the formula.

In the example below, we want to calculate total games played during the month of October: October Wins plus October Losses. The number for October Wins is in cell B4, so we start by typing in =B4. Note that =B4 appears in the formula bar, right above the letters which are used to label the columns. (In this case you'll see the =B4 right above the line between columns C and D.)

Then we type +C4 (where the + sign indicates addition and C4 represents October Losses).

After we press the enter key, the actual number of games (15) appears. However, if you look at the formula bar for cell D4, you'll see the formula (=B4+C4).

Now we'll work on the formula for winning percentage for October. Winning Percentage equals Wins divided by the number of games played. October Wins is in B4, as above. October Games is in D4. Start by typing =B4.

Then type /D4.

When you press the Enter key, the actual winning percentage appears (0.6666667)

Now let's put the formula in for the Season Total. We will use the sum function for this total. The sum function, gives you the total of a series of numbers. The sum function eliminates the need to use the plus sign repeatedly between each of a series of contiguous numbers. The syntax for the sum function is to put the cell addresses to be summed within parentheses following the word sum. An example is shown below where the eventual formula is =sum(B4:B10). This means add together all numbers from B4 to B10, inclusive.

Moving to cell B11, type =sum(.

Then, type in B4 or point to cell B4 (October Wins).

At this point, do one of the following depending on how you input B4:

If you pointed to B4, use the mouse to drag and select the other numbers in the column down to cell B10 (April Wins).
If you typed B4, continue typing :B10 (where the colon indicates that you want to include all the cells between B4 and B10).

Close out the sum function with a parenthesis as shown below.

When you press Enter, the actual total number of wins will appear (51). Once you have input the cell formulas, you can copy the formulas to the rest of the spreadsheet. No need to do any further calculations. It is wise, however, to check your formulas to make sure that they are correct.

Sometimes use of cell formulas give you inconsistent formatting, as shown in column E. Notice above that the number of places shown after the decimal is not the same in each case. You can fix this by hghlighting the formula cells that you want to reformat, then right clicking on the highlighted area. This will give you a context menu as shown below. From the context menu select Format Cells.

The Format Cells dialog will appear. Here, you have many options for how the cells are to be formatted.

In this case, the cells are formatted as numbers with three decimal places. (See above and below.)

Use the links below for tips on how to perform other Spreadsheet functions using Microsoft Excel 97.

Questions and comments to bladams@msn.com