16.11
Microsoft Excel: Finding Central Tendency, Skew, and Kurtosis
Central tendency refers to the central point or typical value of a dataset. It summarizes the data set with a single value that represents the center…
Consider the following three hypothetical datasets plotted in Microsoft Excel.
Now, three measures of the central tendency — mean, median, and mode — can be calculated using the Excel functions AVERAGE, MEDIAN, and MODE.SNGL for the selected range of data.
After visualizing datasets A and C, their tails appear extended to the left and right.
Quantitatively, this skewness can be determined using the Excel function SKEW for the selected range of data. They are calculated as follows for datasets A, B, and C.
Kurtosis can be measured using the Excel function KURT for the selected range of data for the datasets A, B, and C.
Skewness and kurtosis, as measures of asymmetry, determine the departure from symmetry. Skew values closer to zero indicate more symmetry in the data.
Positive kurtosis values indicate a higher peak of the distribution compared to a normal distribution, with fewer values in the center than in the tails.
Conversely, negative values show the distribution's flatness, with more values in the center than in the tails.
View the full transcript and gain access to JoVE Core videos
Q1: What are the three main measures of central tendency in Excel?
The three measures of central tendency are mean, median, and mode. In Excel, calculate the mean using the AVERAGE function, the median using the MEDIAN function, and the mode using the MODE.SNGL function. Each function is applied to a selected range of data. The mean represents the arithmetic average, the median is the middle value when data is ordered, and the mode is the most frequently occurring value.
Q2: How does skewness indicate asymmetry in a dataset?
Skewness measures the asymmetry of data distribution around the mean using the Excel SKEW function. Skew values closer to zero indicate more symmetry in the data. Positive skew shows the right tail is longer, while negative skew indicates the left tail is extended. This quantitative measure determines whether data points are concentrated on one side of the distribution.
Q3: What does positive kurtosis reveal about a distribution?
Positive kurtosis, measured using the Excel KURT function, indicates a distribution with a sharper peak and heavier tails compared to a normal distribution. This means fewer values concentrate in the center while more values appear in the tails. Positive kurtosis reveals a leptokurtic distribution with greater extremity in the data.
Q4: How does negative kurtosis differ from positive kurtosis?
Negative kurtosis indicates a flatter peak with lighter tails than a normal distribution, showing more values in the center and fewer in the tails. This platykurtic distribution contrasts with positive kurtosis, which shows a sharper peak and heavier tails. Both are calculated using the Excel KURT function for the selected data range.
Q5: Why is the median preferred over the mean for skewed data?
The median is less affected by outliers and skewed data compared to the mean, which is sensitive to extreme values. When data is asymmetrical with extended tails, the median provides a more representative central value. This makes the median a more robust measure of central tendency for datasets with significant skewness.
Q6: What do skewness and kurtosis measure together?
Skewness and kurtosis are measures of asymmetry that determine the departure from symmetry in a distribution. Skewness quantifies the direction and extent of tail extension, while kurtosis measures the sharpness of the peak and concentration of values in the tails. Together, they provide comprehensive insight into the shape and extremities of data distributions.
Q7: How can Excel functions help analyze data distribution shape?
Excel provides specialized functions to analyze distribution shape: AVERAGE, MEDIAN, and MODE.SNGL for central tendency; SKEW for asymmetry; and KURT for tailedness. These functions enable performing simple data analysis using Excel functions to quantify how data deviates from symmetry and normality, supporting comprehensive statistical assessment.