Formulas

So let’s dive in. The data we have is not entirely complete. At the moment, healthcare expenditure is only shown as a percentage of GDP. In order to compare total expenditure in different countries, we need to have this figure in US Dollars (USD).

To calculate this, let’s introduce you to spreadsheet formulas.

Formulas are what helped spreadsheets become an important tool. But how do they work? Let’s find out by playing with them…

Tip: Whenever you download a dataset, the very first thing you should do is to make a copy of it. Any changes you should make should be done in this copy – the original data should remain pure and untouched! This means that you can go back and check it at any time. It’s also good practice to note where you got your data from, when and how it was retrieved.

Once you have your own copy of the data (try adding ‘working copy’ or similar after the original name), create a new sheet within your spreadsheet. This is for you to mess around with whilst you learn about formulae.

Now move across to the “Total fruits sold” column. Start in the first row. It’s time to write a formula…

Walkthrough: Using spreadsheets to add values

Using this example data. Let’s calculate the total of fruits sold.

1. Get the data and create a working copy.
2. To start, move to the first row.
3. Each formula in a spreadsheet starts with =
4. Enter = and select the first cell you want to add. Notice how the cell reference appears in the formula?
   

5. Now type + and select the second cell you want to add
   
6. Press Enter or tab .
7. The formula disappears and is replaced by the value.
   

8. Try changing the number in one of the original cells (apples or plums) you should see the value in total update automatically.
9. You can type each formula individually, but it also possible to cut and paste or drag formulas across a range of cells.
10. Copy the formula you have just written (using ctrl + c ) and paste it into the cell below (using ctrl + v ), you will get the sum of the two numbers on the row below.
11. Alternatively click on the lower right corner of the cell (the blue square), and drag the formula down to the bottom of the column. Watch the ‘total’ column update. Feels like magic!

Task: Create a formula to calculate the total amount of apples and plums sold during the week.

Did you add all of the cells up manually?: That’s a lot of clicking – for big spreadsheets, adding each cell manually could take a long time. Take a look at the “spreadsheet formulae” section in the Handbook – can you see a way add a range of cells or entire columns simply?