spreadsheets

Percent Changes and Multiplying and Dividing

You also can use a spreadsheet to calculate percent changes between two numbers.

For example, in our spreadsheet on murder weapons from 2004 to 2008 we can calculate not only how much the use of each weapon increased or decreased, but by what percent each one did so.

This way we can better compare how the use of each type of murder weapon is changing relative to the others.

To do this, we need another formula, in which we'll divide and multiply some numbers to get the percent change.

First let's tidy up the spreadsheet by resizing the G column, in which we previously did calculations of the changes in the numbers of each murder weapon used between 2004 and 2008. This column is much wider than it needs to be for the numbers displayed.

To resize a column, in the gray bar at the top of the spreadsheet where the letters of the columns are displayed, move your mouse cursor to the border between the G and H columns. Your mouse cursor will change from a hand icon to a vertical bar with a right-pointing arrow. Click and drag the bar to the left to decrease the width of the G column.

resizing column

Now let's do the percent calculation, starting with the percent change in the total number of homicides (row 5).

First click on cell H5 to the right.

And type in the following formula:

=(F5-B5)/B5*100

formula in cell H5

This is the formula for calculating the percent change between two numbers.

This formula tells the spreadsheet to subtract the number of homicides by all types of weapons in 2004 (B5) from the number of homicides by all types of weapons in 2008 (F5), then divide that by the original number of homicides in 2004 (B5), and finally multiply the result by 100 (which converts this to a percent figure).

The backslash ( / ) is the symbol for dividing, while the asterisk ( * ) is the symbol for multiplying.

(the parentheses in this formula are also important - see the next section on why we're using those)

Now hit the enter/return key to see the final result of the percent formula in cell H5:

-0.2111189

This shows that the total number of homicides by all types of weapons declined by 0.2 percent from 2004 to 2008 (in other words a decline of about two tenths of one percent).

Note: We could have accomplished this same calculation by using a simpler formula:

=G5/B5*100

That's because we've already set up the G column to calculate the B column minus the F column (that is, to calculate the difference in the number of homicides between 2004 and 2008). So we could have simply divided the number in the G column by the number in the B column and multiplied by 100 to get the percent change.

Now let's apply the percent change formula to the rest of the murder-by-weapon numbers.

Click on cell:

H5

Pass your mouse over the bottom right corner of the cell until the cursor changes to thin crosshairs.

Click and drag the mouse cursor down over the rest of the cells in the H column.

Release your mouse button when you get to cell:

H22

The percent changes for all the different types of weapons used in homicides will appear on your screen.

applying percent change to multiple cells