Excel Removing Outliers from Pivot Table Data

[@TheEngineeringToolboxChannel]

Click Here: [1:43] to skip explanations and background and get straight to the function! :)

[@TheEngineeringToolboxChannel]

Hey everyone! Just to clarify, I was using the terms "quartile" and "quantile" in the video without explaining... A quantile is a GENERAL term for the equal division of a distribution. A quartile is a SPECIFIC type of quantile that breaks the distribution into quarters. The function in excel is for quartiles (i accidentally said quantile). Hope this clearifies! Let me know if there are other questions! :)

[@MohamedElsherifYouTube]

Hi, thanks for the video. Why not adding the hrs/part directly to the quartile function? I am not sure why do we use IF?

[@TheEngineeringToolboxChannel]

Hello Mohamed, Thanks for the question! If I am understanding your question correctly...the reason for the if statement is to determine if the Hrs/Part value is between the upper and lower bounds (fences) or not. A value is considered an outlier if it falls above the upper fence OR below the lower fence. The only way to determine this is with an if function. In plain langue the if statement is saying "if the Hrs/part value is above the upper fence or below the lower fence it is an outlier. If it is between the upper and lower fences it is not an outlier." If this does not answer you question please let me know. I'd be happy to help!

[@supahottfire]

Your Videos area amazing. I am an industrial engineer and find these videos really helpful. Can you please start a series of videos on how to power point presentations?

[@TheEngineeringToolboxChannel]

So glad to hear you enjoy them! and great suggestion...i might do this in the future. I have something else in the *"works"* right now though... ;)

[@blakejustinsmith]

I'm using Excel 2016 but I can't seem to build the array like you are. What are your keystrokes at 2:36 through 2:50. I can't seem to display the ([Part #]=[@[Part #]],[HRS/Part]] to select my columns and build my array. Is this a VBA function? I'm a relatively intermediate user of Excel and need to impose your displayed calculation on a million rows.

[@TheEngineeringToolboxChannel]

Great question. No this is not any kind of VBA function. I often use tables to hold data sets like in this example. They are just easier to work with. If your data is not in a table you will not get the "[Part #]" format like in my example. To select the data for the function, I select the top row of the column i would like to select then hold control+shift and press the down arrow. This selects the entire column. Again, if you are not using a table, the range will not appear as "[Part #]" it will be in the regular excel range format: "A:A1000000". Once your ranges are selected for the function make sure to hit Ctrl+Shift+enter as shown to execute the array function. Another thing I should mention is that array functions will take a VERY long time to calculate with a million rows. The most I ever used them on is 10k or so and it was very slow to calculate so this could be your issue as well. I hope this helps. If you are still struggling feel free to ask more questions. I'm happy to help! :) See my videos on tables: kzbin.info/www/bejne/Y6vZqaFql7alY6c kzbin.info/www/bejne/qYS0l3SidrCSbas (i apologize this is an old video - the first i ever made actually - and the quality is not very good)

[@TheEngineeringToolboxChannel]

Blake, Look into DAX functions and PowerPivot advanced pivot table functions on large data sets. You can get the same outcome as in this demonstration but it is much more efficient for large data sets. Check out this video for an example. kzbin.info/www/bejne/Y3e1kJdomrWoeK8 I may do another video on how to remove outliers from pivot table aggregation soon.

[@raymondhanningtonmuhindi3600]

You are the worst teacher I have ever seen

[@ioia]

@@TheEngineeringToolboxChannelDax function does not have TRIMMEAN 😢

[@michaelamak6364]

Hi, where does the 1.5 come from/signify in the LB/UB calculation? Ive followed this for my own data but im getting negative numbers for my LB figures when i dont have any negative values feeding in anywhere.

[@TheEngineeringToolboxChannel]

By using 1.5*IQR, the % of values between the L/U bound would be roughly the same as +/-3 standard deviations for a normal distribution. The turkey method of characterizing distributions is much less sensitive to outliers and odd shaped distributions though, so we use it as a way of detecting outliers. To Put differently…we use 1.5*IQR because something like 95-99% of the values SHOULD be within the fences almost regardless of distribution shape. So then we just asume if it’s outside that it’s an oddball.