In this blog post, we’re diving into statistics and Excel, focusing on mean and outliers. Before we begin, take a deep breath, hold it for 10 seconds, and let’s get ready to explore the world of statistics in a smooth and soothing manner.
Setting Up Your Excel Worksheet
We’ll start in our Excel worksheet. If you don’t have an existing workbook, don’t worry; we’ll build this from scratch. We have three tabs below: Example, Practice, and Blank. The Practice tab has pre-formatted cells for practicing problems, while the Blank tab contains a data set to work with.
Now, let’s take a look at the Example tab to understand what we’ll be doing. We have salary data on the left, and we’ll perform calculations, including the mean, and create a histogram. We’ll also explore the impact of adding an outlier to the data.
Formatting Your Data
To begin, let’s format our data. Select the entire worksheet, right-click, and format the cells. You can set the number format to currency, remove decimals, and make negative numbers bracketed and red. Bold the entire worksheet in the Home tab’s Fonts group.
Now, let’s insert a table. Go to the Insert tab and choose “Table” to create a table around your data.
Basic Statistical Calculations
Before we proceed, let’s perform some basic statistical calculations. Create a column for the mean/average in column C. Enter “Mean” or “Average” as the header, and adjust the column width if needed. Use the formula =AVERAGE(Table1[Salary]) to calculate the mean. Similarly, calculate the median, max, and min using =MEDIAN(Table1[Salary]), =MAX(Table1[Salary]), and =MIN(Table1[Salary]) respectively.
You can also calculate quartiles if necessary using =QUARTILE.INC(Table1[Salary], 1) for Q1 and =QUARTILE.INC(Table1[Salary], 3) for Q3.
Manual Calculation of Mean
Now, let’s calculate the mean manually to understand the formula. We’ll create a table to show the step-by-step process. In column E, label the first cell as “Mean Calculation (Manual).” In the cell below, write =(Σx) / n to represent the formula for mean.
In the next cell, calculate the sum of values Σx using =SUM(Table1[Salary]), and in the following cell, calculate n using =COUNT(Table1[Salary]). Finally, calculate the manual mean using =(E2 / E3).
Format this table for clarity, and you’ll see that the manual mean matches the Excel-calculated mean.
Creating a Histogram
Now, let’s create a histogram for your data. Select the data and go to the Insert tab, then choose “Histogram” from the charts section. You can customize the chart as needed.
Introducing an Outlier
To see the impact of an outlier, let’s add one to the data. For instance, consider a CEO’s salary of $1 million. You’ll notice that the average is significantly affected by this outlier, causing it to increase.
Handling Outliers
It’s important to recognize that outliers can distort statistics. In certain cases, you may want to exclude outliers or use a different measure like the median, which is less affected by extreme values.
Conclusion
In the world of statistics and Excel, understanding how outliers can influence measures like the mean is crucial. Being aware of when to use the mean, median, or consider excluding outliers is essential for making accurate data-driven decisions. So, remember to use statistics responsibly, and don’t let outliers mislead you in your data analysis journey.