Well, that is it for Data Analysis with Excel.
Skew is a complementary measure of the “ asymmetry of the probability distribution of a real-valued random variable” – details. in fact it’s a “ a measure of the ‘ peakedness‘ of the probability distribution of a real-valued random variable” – details. Kurtosis sounds like an alien disease that infects the crew of the Starship Enterprise. The Standard Error of the mean is calculated by dividing the standard deviation by the square root of the population size. But here’s some pointers for anyone who is interested. In the above example, there are three results that are not covered in this article. Aside from the risk of inaccurate analysis, inserting the formulas would be a useful teaching tool for Excel novices and experts have no way of knowing exactly how Excel has calculated a particular result. Why Microsoft inserts the results in this way is a mystery. That’s a problem because any change in the source data (eg a students score is altered) the statistics are NOT updated as you’d be entitled to expect. The Descriptive Statistics function creates a nicely formatted and comprehensive list of statistics but there’s one big gotcha – it puts numbers in the cells not formulas.įor example, the Mean above is just the number 53 in a cell not the formula =Average(B2:19) which Excel used to calculate it. If Quartiles are needed simple use the corresponding percentile as shown above. So, to calculate any percentile simply enter =PERCENTILE(Range of data, decimal fraction).
Similarly the 2 nd Quartile (identically equal to the 50 th Percentile) can be calculated by entering =Quartile(B2:B19, 2). Therefore 50% of the data lies above 54.5 and 50% below. NB: sorting is not essential for percentiles but it helps us see how Percentile and Quartile work in practice.įor example to calculate the 50 th percentile key in, =Percentile(B2:B19, 0.50) into cell D10 (or any cell) and press enter. Sort the data as shown – from smallest to largest numbers. 1 st quartile = 25 th percentile, 2 nd quartile = 50 th percentile (also the median) and 3 rd quartile = 75 th percentile. Quartiles are simply specific Percentiles, e.g. Percentiles indicate the percentage of values that lie below the percentile value. This implies that students were equally as likely to score above the mean as below it.įinally, there are two other measures that provide information about how the data is spread – they are Percentiles and Quartiles. The spread of values is evenly distributed around the mean and the median.
The mean and the median are relatively close in value (mean = 53 and median = 54.5) indicating that the distribution of results are not significantly skewed to one end. These two values could be outlier values thereby give a false indication of the spread of the data. However the Range is limited by the fact that it only uses 2 values. This indicates how dispersed the data is, considering the test was scored out of 100. As can be seen from the data analysis table, the spread for history scores is from 7 to 100 – equating to a range of 93 marks.