Spreadsheets Tutorial: How far from average?

Key Takeaways

let's now learn how to measure a data points 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 column B 1 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 negative 1 for the second value 14 minus 11 leaves 3 easy huh in the third step square all these differences from the average negative one squares to one and three squared equals nine finally take another average of the squared differences one plus nine plus one plus one equals twelve divided by four equals three that was easy but a bit cumbersome thankfully there is a formula to calculate variance simply call the ARP with an array as shown in this example in which I calculate the variance for all three 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 the VAR p use SQ RT square root to calculate the standard deviation more easily you can pass an array into stdev P 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 and standard deviation was one point seven three now we have a new data point twelve point seven three subtracting the standard deviation twelve point seven three minus one point seven three you get back to the mean of eleven thus this new data point is exactly one standard deviation away from the mean another statistic for understanding a distribution is a percentile ordering a distribution and calculating the percentage of values below a specific point will tell you its percentile this histogram visualizes 1 million values the blue line averaged at zero is the 50th percentile because it splits the data evenly half the points are less than zero and half are greater quartiles are percentiles that segment the data into four chunks the red line at negative 0.67 demonstrates 25% of the data is less than or to the left of negative 0.67 another 25% of the data is greater than negative 0.67 but less than the blue average zero line the next 25% chunk of the data is greater than zero to the right of the blue line but less than the Green Line at 0.67 finally the remaining 25% of the 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 one through four to specify the quartile as you can see here the first quartile is 234 the second is 456 the third is 567 and the fourth is 789 excellent progress now let's