Calculate with Formulas

Spreadsheet tools can save you lots of time when you insert simple formulas and functions to automatically perform calculations across entire rows and columns of data. Formulas always begin with an equal sign, and may simply add up other cells (such as =C2+C3+C4), or may contain a function that performs a specific operation (such as calculating the sum of a range of cells: =SUM(C2:C100)). In this section you’ll learn how to write two formulas with functions: one to calculate an average numeric value, and another to count the frequency of a specific text response. Once again, let’s learn this skill using the reader survey sample dataset we described at the top of the chapter.

  1. Open this Google Sheet of Hands-On Data Visualization reader public survey responses in a new tab in your browser.

  2. Log into your Google Drive account, and go to File > Make a Copy to edit your own version.

  3. Add a blank row immediately below the header to make space for our calculations. Right-click on row number 1 and select Insert 1 below to add a new row, as shown in Figure 3.26.

Right-click on row number 1 and select Insert 1 below.

Figure 3.26: Right-click on row number 1 and select Insert 1 below.

  1. Let’s calculate the average level of reader experience with data visualization. Click on cell E2 in the new blank row you just created, and type an equal symbol (=) to start a formula. Google Sheets will automatically suggest possible formulas based on the context, and you can select one that displays the average for current values in the column, such as =AVERAGE(E3:E2894), then press Return or Enter on your keyboard, as shown in Figure 3.27.
Type = to start a formula and select the suggestion for average, or type it directly in with the correct range.

Figure 3.27: Type = to start a formula and select the suggestion for average, or type it directly in with the correct range.

Since our live spreadsheet has a growing number of survey responses, you will have a larger number in the last cell reference to include all of the entries in your version. Currently, the average level of reader experience with data visualization is around 2 on a scale from 1 (beginner) to 5 (professional), but this may change as more readers fill out the survey. Note that if any readers leave this question blank, spreadsheet tools ignore empty cells when performing calculations.

Tip: In Google Sheets, another way to write the formula above is =AVERAGE(E3:E), which averages all values in column E, beginning with cell E3, without specifying the last cell reference. Using this syntax will keep your calculations up-to-date if more rows are added, but it does not work with LibreOffice or Excel.

  1. Part of the magic of spreadsheets is that you can use the built-in hold-and-drag feature to copy and paste a formula across other columns or rows, and it will automatically update its cell references. Click in cell E2, and then press and hold down on the blue dot in the bottom-right corner of that cell, which transforms your cursor into a crosshair symbol. Drag your cursor to cell F2 and let go, and show in Figure 3.28. The formula will be automatically pasted and updated for the new column to =AVERAGE(F3:F2894) or AVERAGE(F3:F), depending on which way you entered it above. Once again, since this is a live spreadsheet with a growing number of responses, your sheet will have a larger number in the last cell reference.

Figure 3.28: Click on the blue bottom-right dot in cell E2, then hold-and-drag your crosshair cursor in cell F2, and let go to automatically paste and update the formula.

  1. Since the Occupation column contains a defined set of text responses, let’s use a different function to count them using an if statement, such as the number of responses if a reader listed “educator”. Click in cell G2 and type the equal symbol (=) to start a new formula. Google Sheets will automatically suggest possible formulas based on the context, and you can select one that displays the count if the response is educator for current values in the entire column. You can directly type in the formula =COUNTIF(G3:G2894,"=educator"), where your last cell reference will be a larger number to reflect all of the rows in your version, or type in the Google Sheets syntax =COUNTIF(G3:G,"=educator") that runs the calculation on the entire column without naming a specific endpoint, as shown in Figure 3.29.
Select or enter a formula that counts responses if the entry is educator.

Figure 3.29: Select or enter a formula that counts responses if the entry is educator.

Spreadsheet tools contain many more functions to perform numerical calculations and also to modify text. Read more about functions in this support pages for Google Sheets, LibreOffice, or Microsoft Excel support page.

See additional spreadsheet skills in later chapters of the book, such as how to find and replace with blank, split data into separate columns, and combine data into one column in Chapter 5: Clean Up Messy Data. See also how to pivot address points into polygons and how to normalize data in Chapter 14: Transform Your Map Data.

Now that you’ve learned how to count one type of survey response, the next section will teach you how to regroup data with pivot tables that summarize all responses by different categories.