Spreadsheets Tutorial: How far from average?

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

---
Let's now learn how to measure a data point's distance from the average.

The exercises following this video will explore US train ridership to understand how it varies over time. So jump aboard the stats train!

Variance measures how dispersed a dataset is from its mean. The smaller the variance, the less spread the data is. Conversely, large differences between data points increase the variance. Column A repeats with no variation. Its variance is 0.

In B, one value - 14 - is different yet close to the others.

Its variance is 3. Column C has an outlier - 100. As a result, its variance is the highest among the three. To calculate variance, first calculate the mean. 10,14, 10 and 10 divided by 4 equals 11. Next, subtract the mean from each value. For the first, third, and fourth values, 10 minus 11 is -1. For the second value, 14 minus 11 leaves 3. Easy huh? In the 3rd step, square all these differences from the average. -1 squares to 1, and 3 squared equals 9.

Finally, take another average of the squared differences, 1+9+1+1=12 divided by 4 equals 3. That was easy, but a bit cumbersome. Thankfully there is a formula to calculate variance.

Simply call VARP with an array, as shown in this example in which I calculate the variance for all 3 columns.

Next stop Standard Deviation! Keep in mind variance is the average of squared values. Thus the variance is different from the original sample values making it less intuitive! Most often you will need to make sense of the variation by putting it in the scale of the original data. This is done by taking the square root of the variance, called standard deviation.

After taking the variance with VARP can use SQRT, squareroot, to calculate the standard deviation. More easily you can pass an array into STDEVP to get the same answer, here 1.73.

Standard scores show you how a data point relates to the distribution. Our previous population mean was 11 & standard deviation was 1.73. Now we have a new data point 12.73. Subtracting the standard deviation 12.73 - 1.73 you get back to the mean of 11. Thus this new point is exactly one standard deviation away from the mean.

Another statistic for understanding a distribution is a percentile. Ordering a distribution & calculating the percentage of values below a specific point will tell you its percentile. This histogram visualizes 1 million values. The blue line average at 0 is the 50th percentile because it splits the data evenly. Half the points are less than 0 & half are greater.

Quartiles are percentiles that segment the data into 4 chunks. The red line at -0.67 demonstrates 25% of the data is less than or to the left of -0.67. Another 25% of the data is greater than the -0.67 red line but less than the blue average 0 line. The next 25% chunk of the data is greater than 0, to the right of the blue but less than the green line at 0.67. Finally, the remaining 25% of data points are greater than 0.67 to the right of the green line.

To get the popular percentiles in sheets use the QUARTILE function accepting an array then a number 1-4 to specify the quartile. As you can see here, the first quartile here is 234, the second is 456, the third is 567, and the fourth is 789.

Excellent progress! Now, let's practice.

#DataCamp #SpreadsheetsTutorial #Statistics #Spreadsheets

Видео Spreadsheets Tutorial: How far from average? канала DataCamp