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.
Let’s explore a large dataset of around 3,000 readers of this book who responded to a quick public survey about their general location, prior level of experience and education, and goals for learning data visualization. If you haven’t already done so, fill out the quick survey form to contribute your own response, and also to give you a better sense of how the questions were posed.
Open this Google Sheet of Hands-On Data Visualization reader public survey responses in a new tab in your browser.
Log into your Google Drive account, and go to File > Make a Copy to edit your own version.
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 2.22.
- 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 2.23.
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.
- 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 2.24. The formula will be automatically pasted and updated for the new column to
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.
- 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 2.25.
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. Chapter 4: Clean Up Messy Data demonstrates how to find and replace, split data into columns, and combine columns of data (such as when you need the street address, city, and postal code all in one line). Chapter 11: Transform Your Map Data also features more advanced spreadsheet skills and tools, such as how to geocode addresses, pivot address points into polygons, and how to normalize data to create more meaningful polygon maps.
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.