spreadsheets

Percentages of a Constant Number

Another calculation you can perform with a spreadsheet is determining the percentage of a fixed number.

In our spreadsheet of weapons used in homicides, for example, we might want to know what percentage of the total number of homicides involved each different type of weapon.

Thus for the year 2008, we'd want to know what percentage of the total homicides that year (cell F5) was caused by each of the 17 different categories of weapons (cells F6 through F22).

F column and fixed percentage calculation

We can start by calculating the percentage of murders involving all kinds of firearms (cell F6) and then applying that same formula to the cells for the other types of weapons.

To create our percent formula click on cell:

G6

And type in this formula:

=F6/F5*100

This formula tells the spreadsheet to divide the number of homicides involving firearms in 2008 (F6) by the total number of homicides that year (F5), and then multiply the result by 100 (which converts this to a percent figure).

Press the enter/return key and you'll see the total is:

66.88...

So firearm related homicides were about two thirds of the total number of homicides in 2008.

You might then try to apply the same calculation to the cells for the other types of weapons using the applying a formula to multiple cells method we covered earlier.

Thus you might click on the crosshairs in the bottom right corner of cell G6 and drag the formula to the cells below, on down to cell G22.

But this will produce bizarre numbers in the G column, including that some weapons-related homicides are more than 100% of the total.

nonsensical numbers from formula

What went wrong?

The problem is that when the spreadsheet copies a formula using this method, it shifts the letters for both cells in the original formula (F6 and F5) as it applies that formula to other cells (resulting in F7 divided by F6 in the next cell down).

But we want the number in cell F5 (the total number of homicides) to always remain constant in the formula.

To fix this, we need to force the spreadsheet to always divide the numbers for each type of weapon used by a constant number – the total number of homicides in cell F5.

To do this we need to anchor the F5 cell in our formula, and force the spreadsheet always to divide the number for each particular type of weapon by that same total number of homicides in F5.

You accomplish this by adding some $ signs to the formula that instruct the spreadsheet not to change cell F5 when applying the formula to other cells.

So go back and click on cell:

F6

Which is where we typed the original formula: =F6/F5*100.

Delete that formula, and instead type in this:

=F6/$F$5*100

The dollar signs tell Excel to always keep anchored on cell F5 and the data in it when applying this formula to other cells.

fixed formula in cell F6

When you now press the enter/return key you’ll see the same number in the F6 cell as before:

66.88...

But now we can drag the formula down through the column of cells and get the correct results.

So hover your mouse over cell:

F6

Then click on the crosshairs in the bottom right corner of the cell and drag down to cell:

F22

And release your mouse.

correct formula applied to all cells

The correct percentage figure for each weapon type will now appear in the spreadsheet.

Thus the percentage of homicides in 2008 involving a firearm of any kind was about 66.9 percent of the total, those involving a handgun was about 47.6%, knives or cutting instruments about 13.4%, strangulation about 0.6%, and so on.