Excel provides several useful statistical functions that allow you to calculate descriptive statistics. In our dataset, we have thousands of values for each numeric attribute, which makes it difficult to observe the behavior of any attribute as the data size increases.
In this segment, you will learn about the various statistical functions available in Excel and understand how transit storage and shipping costs vary.
Let’s look into the statistical function demonstrations.
In the video above, you saw functions such as AVERAGE, MEDIAN, STDEV, and PERCENTILE functions. Using these functions, we calculated the summary statistics of transit storage and shipping cost.
The image below shows the formulas used for the calculations along with two other useful functions, MIN and MAX. These functions provide the minimum and maximum values for a numeric column, respectively.
Columns J and K contain transit storage costs and shipping costs, respectively. The Interquartile Range (IQR) is the difference between the 25th and 75th percentile and provides an idea of the variability in the data. You also observed an extreme value of 9999, which caused a significant difference between the mean and median.
In the next video, you will learn how to identify outliers using the statistical results from the previous demonstration. The cutoff values are defined as three standard deviations away from the mean, which is a common business rule of thumb. Theoretically, 99.9% of values should lie within ±3 standard deviations, so values exceeding these cutoffs are considered outliers.
In the following video, Vivek Dhumal will explain the COUNTIF function and its applications.
In the video, we calculated the number of outliers by defining the upper and lower cutoff values. These cutoffs were set at three standard deviations above and below the mean. The COUNTIF function was used to count the number of values exceeding the cutoff thresholds. You will learn more about this function in the next segment.
The image below illustrates all the formulas used in this segment, along with additional formulas to determine the minimum and maximum values for transit storage and shipping costs.
Click here to learn more about statistical functions and their syntaxes in Excel. Attempt the following questions to check your understanding of the concept.
Note: Use Superstores_data to solve the following questions.
Let’s move on to the next segment where you will learn about Logical Functions.