The Mathematics of Money

212 Chapter 5 Spreadsheets

Edit Paste (or click on the paste icon.) You should see the correct amounts now in the

product sales column.^1

Here, we exploited of one of the biggest advantages of spreadsheet programs. When

we copied D2 to D3, we didn’t mean we wanted an exact copy of D2 in D3. We didn’t

want D3 to contain “
B2*C2”. We wanted it to have a formula that would do the same

sort of thing, but one row down: “
B3*C3”. Fortunately for us, when you copy a formula

the spreadsheet program automatically assumes that if you are moving the formula down

one row, you must want all the cells in that formula to be moved down a row as well. If

you highlight D3 and look in the edit line, you will see that the formula in D3 is what we

wanted, “
B3*C3”. Likewise, the formula in D4 is “
B4*C4”. The advantage of being

able to do this should be obvious; even if we were working with 1,000 different products in

1,000 rows, we could still set up a spreadsheet to calculate the product sales for each and

every row easily and quickly just by copying and pasting.

The spreadsheet should now look like this.

1

2

3

4

5

A B D

Product Units Sold

Solar Singing Fish Gutter 1800

Home Crash Test Kit 880

Twelve Tone Door Chime 500

Totals

C

Unit Price

$23.95

$127.95

$44.99

Product Sales

43110

112596

22495

Lastly, we want to set up the program to put the totals in the last row. We can once again

do this with a formula. In B5, enter the formula “
SUM(B2..B4)”. This formula tells the

program to add up all the values in the range of cells from B2 to B4. We can then copy this

into cell D5 to get the sum of the product sales. (We won’t do this in column C, because a

sum of all the unit prices is probably not something anyone would be interested in, so we

can just put the text “N/A” in that cell.)

At this point, you may want to format column D. Since the dollar amounts are fairly

large, it makes sense to format this column for 0 decimal places (so it will display the

amounts rounded to the nearest dollar.) You may want to bold the total row as well to make

it stand out more. The result, shown here is the table we set out to create.

1

2

3

4

5

A B C D

Product Units Sold Unit Price

Solar Singing Fish Gutter 1800 $23.95

Home Crash Test Kit 880 $127.95

Twelve Tone Door Chime 500 $44.99

Totals N/A

Product Sales

$43,110

$112,596

$22,495

3180 $178,201

Making Changes in a Spreadsheet

Suppose that, after building this spreadsheet, we realize that we had inaccurate information.

There were actually 1,500 door chimes sold, not the 500 we put in our original spreadsheet.

This will change the total sales for that product, the total units sold, and the total overall

sales of the company. Just this one change will result in many other changes.

(^1) There are also shortcut ways to copy and paste, depending on the version of the program you are using. In this text,
we are giving the steps to do most things from menu selections, since those steps will work on just about any version
of Excel. However, your instructor may show you some shortcuts that can be used with the specifi c spreadsheet
program that you are using.