How to find Outliers in Your Data Easily with Z-Scores in Excel
24.6 هزار بار بازدید -
7 ماه پیش
-
Do you want to find
Do you want to find out if there are any outliers in your data? You can use simple Excel formulas to calculate z-scores and identify the outliers.
~
To identify the outliers, we need to first calculate the z-scores of every data point. To do this, you need to calculate the average and standard deviation of the data first.
Z-score of a data point is defined as (data point value - average)/standard deviation.
Once all the z-scores are calculated, we then consider any data point with absolute z-score higher than 3 as an outlier.
We can further enhance the z-score analysis by calculating segment level z-scores. For example, to identify which product sales are outliers, we can calculate the z-scores by finding the average and standard deviation of product sales for that product category alone (instead of all data points) and then use the values to identify the outliers (segment-level z-score higher than 3). This will help you identify the correct outliers for business datasets.
📁 Download the sample file with calculations here - https://chandoo.org/wp/wp-content/upl...
❓ Alternative Standard Deviation formula for older Excel:
=STDEV.P(IF($C$7:$C$229=C7,$E$7:$E$229))
and press CTRL SHIFT Enter.
~
⌚ Topics in this video:
===================
0:00 - Using z-scores to find the outliers in your data in Excel
0:28 - Calculating the average and standard deviation of the data
1:39 - What is z-score?
1:53 - Calculating Z-score in Excel
2:19 - Definition of an outlier (in normal data)
3:06 - Identifying outliers in the data
4:05 - Problem with z-scores and how to fix that?
5:08 - Average and Standard deviation at product level
7:13 - Z-scores at product level
8:33 - Highlighting outlier values with conditional formatting rule
10:01 - Predicting (forecasting) future values with Excel
📈 Forecasting with Excel
=====================
Article: https://chandoo.org/wp/forecasting-in...
Video: Introduction to Forecasting in Excel
💻💡 Data Analysis with Excel (more videos)
=====================================
Check out below videos for more data analysis tips, examples and explanations with Excel
5 Key skills you need to be a data analyst - 5 key skills you need to become a GRE...
Excel Data Analysis - Beginner to PRO video - Beginner to Pro FREE Excel Data Analy...
Budget vs. actual analysis with Excel - Make a BEAUTIFUL Budget vs. Actual ch...
Clean, analyze and present business data - How to Clean, Analyze and Present Dat...
Full Data Analysis with Excel Course - https://chandoo.org/wp/excel-school-p...
~
Knock, knock...
Who's there?
Outlier...
Ok, you stay there 😂
#excel #statisticas #zscore
~
To identify the outliers, we need to first calculate the z-scores of every data point. To do this, you need to calculate the average and standard deviation of the data first.
Z-score of a data point is defined as (data point value - average)/standard deviation.
Once all the z-scores are calculated, we then consider any data point with absolute z-score higher than 3 as an outlier.
We can further enhance the z-score analysis by calculating segment level z-scores. For example, to identify which product sales are outliers, we can calculate the z-scores by finding the average and standard deviation of product sales for that product category alone (instead of all data points) and then use the values to identify the outliers (segment-level z-score higher than 3). This will help you identify the correct outliers for business datasets.
📁 Download the sample file with calculations here - https://chandoo.org/wp/wp-content/upl...
❓ Alternative Standard Deviation formula for older Excel:
=STDEV.P(IF($C$7:$C$229=C7,$E$7:$E$229))
and press CTRL SHIFT Enter.
~
⌚ Topics in this video:
===================
0:00 - Using z-scores to find the outliers in your data in Excel
0:28 - Calculating the average and standard deviation of the data
1:39 - What is z-score?
1:53 - Calculating Z-score in Excel
2:19 - Definition of an outlier (in normal data)
3:06 - Identifying outliers in the data
4:05 - Problem with z-scores and how to fix that?
5:08 - Average and Standard deviation at product level
7:13 - Z-scores at product level
8:33 - Highlighting outlier values with conditional formatting rule
10:01 - Predicting (forecasting) future values with Excel
📈 Forecasting with Excel
=====================
Article: https://chandoo.org/wp/forecasting-in...
Video: Introduction to Forecasting in Excel
💻💡 Data Analysis with Excel (more videos)
=====================================
Check out below videos for more data analysis tips, examples and explanations with Excel
5 Key skills you need to be a data analyst - 5 key skills you need to become a GRE...
Excel Data Analysis - Beginner to PRO video - Beginner to Pro FREE Excel Data Analy...
Budget vs. actual analysis with Excel - Make a BEAUTIFUL Budget vs. Actual ch...
Clean, analyze and present business data - How to Clean, Analyze and Present Dat...
Full Data Analysis with Excel Course - https://chandoo.org/wp/excel-school-p...
~
Knock, knock...
Who's there?
Outlier...
Ok, you stay there 😂
#excel #statisticas #zscore
7 ماه پیش
در تاریخ 1402/09/13 منتشر شده
است.
24,681
بـار بازدید شده