How to find Outliers in Your Data Easily with Z-Scores in Excel

Подписаться 620 тыс.

Просмотров 24 тыс.

50% 1

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 - 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: chandoo.org/wp/forecasting-in...
Video: • Introduction to Foreca...
💻💡 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 ...
Excel Data Analysis - Beginner to PRO video - • Beginner to Pro FREE E...
Budget vs. actual analysis with Excel - • Make a BEAUTIFUL Budge...
Clean, analyze and present business data - • How to Clean, Analyze ...
Full Data Analysis with Excel Course - chandoo.org/wp/excel-school-p...
~
Knock, knock...
Who's there?
Outlier...
Ok, you stay there 😂
#excel #statisticas #zscore

Наука

Опубликовано:

6 июл 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 39

@FemiDanalyst 7 месяцев назад

Thank you mentor. This is educative and I can't wait to use it

@IvanCortinas_ES 7 месяцев назад

Excellent explanation. Thank you!!!

@Bra_solo 7 месяцев назад

lowkey this guy created excel.what a gem

@carltonlobo 7 месяцев назад

this is something new that I have learned, please update a series related to such learning

@Seftehandle 7 месяцев назад

I wish we had learn statistics this way Very well explained

@TheMrKlowb 7 месяцев назад

More videos on using excel for statistics would be AMAZING! Thank you for all the content!🤙

@srikrishnanrengarajan6109 7 месяцев назад

Excellent video and mentoring sir

@johanneskeller3794 7 месяцев назад

Very helpful explanation useful for my statistical calculations I habe to cope with in my daily practical work.

@alexrosen8762 4 месяца назад

Superuseful 👌Thanks Chandoo 🙏

@rohit206 7 месяцев назад

Hi Chandu, excellent video. I was always scared of learning statistics but your video made it so simply to understand. Would request you to start a series of similar videos where concept of statistics are explained through excel.

@RobynLynn07 Месяц назад

Simply amazing ❤ thank you!

@chandoo_ Месяц назад

Glad you like it!

@VipulNegi9 3 месяца назад

brother you are great

@premkumarreddy8889 7 месяцев назад

Thanks sir

@anands06 6 месяцев назад

Thanks for posting, can you also make more videos on how to apply Excel analysis in supply chain data like transportation, warehousing etc.

@eugenelee591 7 месяцев назад

Thanks for the video! You broke down a complicated concept and made it easy to understand. I have 2 questions after watching: 1. Why was it important to identify the outliers based on individual product category? What is the difference in those two z-scores you calculated and what does that mean? 2. How do you determine what to do with the outliers after you identify them?

@chandoo_ 7 месяцев назад

Thank you. The analysis of outliers depends on actual business situation. But in this particular case, 1) As our products have different sales volumes (for ex. 50% dark chocolate always sells more than Almond choco), finding outlier within segment is a better way to understand what is a truly exceptional value. Same logic applies for situations like analysing daily sales (but you ran a 50% discount sale for one week in December). 2) Once you identify the outlier values, you can investigate the reasons for such low or high values. Some of these could be pure randomness, but there might a strong business driver behind it too. May be you the sales team had an exceptional week or the current promotions you ran yielded better than normal results. Hope that helps.

@v_fiver 7 месяцев назад

Sir plz make video on how to use trim on all data sheet

@prashantpareek8127 Месяц назад

Chandoo can you please make an entire series on Stats Zscore, Tscore,P value, Null hypothesis and not able to understand any of this

@nagarajqs07 7 месяцев назад

Thank you Chandu sir. I am from Hyderabad

@bhmsupra 6 месяцев назад

Who Cares

@haraldniederstatter4068 7 месяцев назад

many thanks for this excellent video! Do you also have a recommendation for what to do if the data is not normally distributed? Thanks again!

@chandoo_ 7 месяцев назад

You are welcome. The outlier detection techniques can get tricky with other distributions when using Excel. You can implement KNN technique for most datasets. Please read up on this and if possible use a tool like Python to quickly identify them. Excel 365 now supports native Python code.

@vikaspawar6494 6 месяцев назад

Hi, Can Market Basket Analysis be done in Excel, If yes, may i request you to consider making a video on it

@preetshah3793 6 месяцев назад

Make more videos on statistics for data analysis

@kage2g 6 месяцев назад

🔥🔥🔥

@bindukundu7122 7 месяцев назад

Sir please can you tell us which tool we should learn firstly from Power BI and Tableau as a beginner.I am little bit confused sir and also what we can use Power BI on MacBook please make a video on these two confusions clear that may be this same question for somebody also .I request you sir clear that point asap.

@mahmodi5timetolearn 7 месяцев назад

Full python sklearn library is summarized in 10 minutes

@560cvishnuteja3 6 месяцев назад

Sir how to create ids for a country, product and sales persons differently in sales table

@CluelessInChicago 6 месяцев назад

👍

@bayurezkipratama6247 6 месяцев назад

do we need to test the normality of the data before making this analysis? 🤔

@aijazahmad6779 7 месяцев назад

Have offline course @chandoo sir

@bhmsupra 6 месяцев назад

This Z score only seems to be finding the items that are 3X greater than Std Deviation avg. I have items in a list that are 3X less than Std Deviation avg also. How do I handle these?