Topic

Stories in numbers: Calculating percentages

Topic Progress:

Click on the numbered tabs below to navigate this lesson.

This lesson has been adapted from materials prepared for the Sudan Evidence Base Programme, created by Eva Constantaras with support from the World Bank and the UK Department for International Development (DFID).

Percentages are one of the most universally understood ways of expressing the relationship between numbers. If we say 30% of Sudanese GDP is generated via agriculture, the meaning is clear (so long as you know what GDP is, of course).

Calculating percentages can present journalists with problems, however. If the number of workers in a factory goes up from 100 to 125 over the course of a year, it’s obvious that it has increased by 25%. But if it drops from back down from 125 to 100 the next year, there’s been a 20% decrease in the number of employees.

Why? Because 25 is a quarter of 100 (ie. 25%), but only a fifth of 125 (20%). Using the right base number to calculate changes often catches writers out – but it’s not that hard to get right.

For the purposes of this lesson, we’re going to be using the Google Sheets application to show you how to calculate percentages using columns in s spreadsheet. A vital skill for journalists. Sheets is free to use so long as you have a Google (ie Gmail) account, and runs inside a web browser. Any changes you make are automatically saved on Google’s servers and can be accessed from any networked PC or mobile device.

Other spreadsheet software with similar features that can be used offline include Microsoft Excel (paid for) and Libre Office Calc (free).

This lesson follows on from Stories in numbers: Sums and rates, it is highly recommended that you read through that lesson first.

In this lesson you will learn:

  • How to calculate percentages in spreadsheets
  • How to convert decimal fractions to percentages in spreadsheets

Calculating Percentages

Just like ratios, percentages are a simple way to communicate the relationship between numbers.

Using the Google Sheet that you created in the previous lesson, let’s figure out what percentage of total midwives in Sudan live in the region with the most midwives, Gazeria.

To calculate a percentage, you start by dividing the number you want to be expressed as a percentage amount by the total for the whole. So in our case, we need to divide “Midwives in Gazeria” (value in cell C6) by “Midwives in Sudan” (value in cell C19).

  1. In cell H1, add a label called Percentage of Midwives
  2. In cell H6 (the row for Gazeria), enter the formula =C6/$C$19 and press Enter.

Why the new formula?

You should have noticed that the Sheets formula we used there was slightly different to the one from last lesson. Instead of writing =C6/C19, we wrote =C6/$C$19.

The reason is that if we wrote =C6/C19, and then used our cross shaped cursor to repeat it in the whole column of cells, both cell references would change each time it is repeated and we wouldn’t be dividing by the total population of Sudan any more. So the formula in cell D6, for example, would become =D6/C20, which we don’t want (C20 is empty).

You can try this for yourself to see what we mean.

The dollar sign in =C6/$C$19, however, tell Sheets that we want both the row and column references that follow the $ in this formula to be locked and unchanged when we copy it to another cell.

If you don’t lock this value, the calculation won’t work.

Next, hover over the bottom right corner of cell H6 until a black cross appears. Click and hold, and drag it down across the rows to H19. The cells H1:H4 will still be blank – you can click and hold the cross again and drag it up the column to fill these and create the column of ratios.

Tip: If you’re working with a lot of rows of data, double clicking the black cross in the bottom right of a cell will automatically fill all cells below that one to the end of the dataset, just as if you’d dragged the selection box to the bottom of the column. Try it and see – don’t forget you can experiment and use CRTL+Z to undo.

Converting to percentages

Now we have a decimal fraction that represents the proportion of midwives in a state to the national total, we need to convert this to a percentage.

You can select all the cells in a column by clicking on the letter at the top of the table. Do this for column H. Now go to the Format menu at the top of the screen and select Number>Percent.

You can now see that 2.02% of Sudan’s midwives live in Khartoum

Percentages test

Now that you know how to calculate percentages using Google Sheets, try these exercises.

Struggling? See next page for more hints.


Percentages test (2)

If you completed the exercises on the previous page, you should have a table that looks something like this. The formula for the final column starts =D2/$D$19.

If you’re not sure why, or are getting a different result, go back a few slides and work through the exercises again.

Conclusion

Review the answers to each of your five questions in the opening lesson of this section. Were you able to prove your hypothesis true or not? Write the lead to your story based on your findings.

Write five more questions you could ask if you had the following data:

  • The budget for maternal healthcare in each state.
  • The average salary of health visitors and midwives in each state.
  • The maternal death rate for each state.
  • The infant death rate for each state.