Spreadsheets Tutorial: Standardizing data

Want to learn more? Take the full course at learn.datacamp.com/courses/introduction-to-stat… at your own pace. More than a video, you'll learn hands-on coding & quickly apply skills to your daily work.

---
In this lesson, you'll learn how to standardize your data.

Why do this? Many real-world datasets you'll encounter will often have variables that are measured on different scales. For example, height might be measured in feet, while weight might be measured in pounds. This poses a problem, because variables on different scales are harder to compare, and it may lead you to misinterpret the importance of a particular column - that column may appear more important simply because it has larger values than another, when in reality, it may actually have a very similar distribution to the column with smaller values.

The solution to this problem is to standardize your data so that all your variables are on the same scale. In statistics, standardization centers a dataset's distribution around the mean of the data and calculates the number of standard deviations away from the mean each point is.

You can standardize your data by calculating z-scores, also known as standard scores. Z-scores are an extension of what you've already seen in this chapter.

To calculate the z-score of a data point, subtract the mean and divide by the standard deviation, as shown on this simple example here, in which we have 3 data points.

First, we need to calculate the mean, using the AVERAGE formula, and then the standard deviation, using STDEVP.

Let's add this information into 2 new columns.

To calculate the z-score, we then need to subtract the mean from each data point, and divide by the standard deviation.

But you probably don't want to calculate this manually, as we're doing here.

Just as with the standard deviation, variance, mean, median, and other statistics you've seen so far, there's a spreadsheets formula that makes it easy to calculate z-scores.

In the STANDARDIZE function, you need to pass in the data point, the mean, and the standard deviation, as shown here.

and the standard deviation, as shown here.

Let's say we had another set of data points that are 10 times larger. As you can see here, while the standard deviation and mean are different - 10 times larger -the z-scores are exactly the same! Despite being 10 times larger, the distances of each data point to their respective sample's mean & standard deviation are the same as in the first column, and this allows you to easily compare the two columns. In the exercises, you'll have the opportunity to practice standardizing your data.

Almost done with chapter 1. You're a stats rockstar already!

#DataCamp #SpreadsheetsTutorial #Statistics #Spreadsheets