How to calculate relative numbers

This topic assumes some familiarity with spreadsheet applications. If you need more help with mastering the basics, see our forthcoming Google Sheets tutorials.

Now that we've established why we use relative numbers to compare data, the next question is how do we calculate relative numbers.

For this exercise, we are going to look at the number of schools in South Africa. The Department of Education maintains several lists of schools data on its website. We've used this very large dataset to count the number of public schools in each province that offer secondary education. This means both secondary schools and combined schools. In total, there are 8175 schools around the country.

You can view this spreadsheet for yourself here. To follow along with the next steps, you'll need to create a copy of it either by clicking File>Download (for use in a desktop application) or File>Make a copy (for use in a Google Sheet).

This list tells us that there are more schools in KZN than any other province offering secondary-level education, but by itself that doesn't mean much. We need understand the ratio of schools to population to understand more about how schools are distributed.

We can add a third column to this sheet, showing population data, using StatsSA's 2021 population estimate. That gives us a table that looks like this:

Now, with these two measurements, we can use column D to calculate the number of schools per person in the province. This is a simple calculation, which is # of schools/# of people. In our spreadsheet, it can bne done in cell D2 with the formula =sum(B2/C2).

Press return, and you should see the value 0.00295210591 in cell D2. Place your mouse cursor over the bottom right corner of that cell, where it should turn into a cross, and than click and drag it down to cell D11. This copies the formula to all of the cells below, automatically changing B2 to B3 and C2 to C3 depending on which row the formula is in.

Now you have the number of schools per person, but these very small numbers are hard to read and compare. In these cases, a slightly different ratio is often used: per 100 000 people. To calculate this, we'll create a new formula in column E that multiplies column D by 100 000. In E2, this will look like =sum(D2*100000).

Finally, we want to round evenrything to two decimal places, so we are going to click in the E at the top of the column, then click the Format menu and select Number>Number 1000.12

Now we have a table that shows us very clearly the ratio of schools to population, which we can sort from top to bottom. You'll find this on the second page of our spreadsheet for you to compare with your work.

You'll see that although KZN has the most secondary schools of any province, it's actually the Eastern Cape which has the most per 100 000 people, and Gauteng that has the fewest. We might begin to guess that this is because of population density - in rural areas there are a lot of small schools spread out over a wide area, but in big cities the concentration of people means one big school can serve five times as many students (or more). We would need some more data and expert advice to confirm this hypothesis, though.