Descriptive statistics in Excel
Excel provides a great feature called descriptive statistics to quickly provide statistical data like sample variance, standard deviation, etc. from your data with a few clicks. Click on ‘Data’ and then ‘data analysis’ to perform analysis of your data. The various parameters are described in detail below:
Mean: The mean is the average of all the data. We could also obtain the data’s mean by applying the AVERAGE function.
Median:The median is the 50th percentile of the data. We could calculate the median by using the MEDIAN function.
Mode: The mode is the most frequently occurring value in our data. Mode can also be calculated using the MODE function. If no value in your data occurs more than once the mode function returns #NA.
Kurtosis: Kurtosis near 0 means the data set exhibits a curve close to the standard bell-shaped curve. Positive kurtosis means that the data values is more ‘peaked’ than a normal curve and negative kurtosis signifies that tha data values are less ‘peaked’ than the normal curve.
Sample variance: deduct the data point value from the mean for each value, square the difference of each value, add the squared differences and finally divide by the total number of data values minus 1 (n-1). This gives us the sample variance. The value in cell b4 is 95, the average is 94.59, the difference is 0.41. The square of 0.41 is 0.1681. Dividing the sum of the squared values by the total count of the values minus 1 (39-1=38), we get 6.300944669.
Standard Deviation: It is the square root of the sample variance.
Range: If you subtract the smallest number from the largest number from the data values you get the range. In our case it is 9 for the data values of School A.
What information do the mean and standard deviation provide? The bell shaped curve describes some of the data:
About 68% of all observations fall between mean-standard deviation and mean+standard deviation. Approximately 95% of all values are between mean-2*standardeviation and mean+2*standard deviation
About 99.7% of all data lie between mean-3*standard deviation and mean+3*standard deviation.
In our example of School A about 68% (26-27) of students got marks between 94.58974359+-2.51 marks i. e. about 13 students got between 92.08 and 94.59 marks and about 13 students got 94.59 to 97.1 marks and 95% (37) students got marks between 89.57 and 99.61 and so on.