make a boxplot in excel 2010 with outliers

So I have designated cell D3 in the spreadsheet as my tolerance level. Okay, my solution uses a spreadsheet cell to designate what the tolerance is for an outlier. Come back tomorrow to see my advanced solution.Ģ) Designate a Cell as the Outlier Tolerance Level Now here is my basic solution for creating an Excel Line that doesn’t display Outliers. I had to include 500% and 100% to allow the user to show the original data as the data point “2” is 710% away from the calculated theoretical value.“Ĭheck out his solution here: Petes Outlyer Solution This way the user can choose at what percent at which to remove the outlyers. Now I used and IF formula to return a #N/A error if the real value and theoretical value differed by a given % chosen by the data validation drop down cell. With this formula and the calculated slope, I could solve for B. You will have to unlock the sheet to view the formulas.īasically, I took the original data and used the SLOPE function to find the slope of the data, and then compared it to a theoretical line based on the X value using the algebraic formula for lines Y=MX+B. My chart shows the original data, and the new data with the outlyers removed. Here is his solution in his own words:įrom Pete: “I was playing around with your new Friday challenge, and I came up with a different spin on the results. He takes the data points and creates a formula based on the slope of the line.

Pete had an ingenious way around this problem. Finally, below that you will find a copy of my spreadsheet that you can download to plan around with the data and charting technique.įirst off, let me show you how another user tackled this problem.

Below that you will see a Video demonstration of this Excel tip. Below that is a detailed step-by-step tutorial of this Excel solution.

So how can we create the final chart by removing the data points from January 4th and January 7th without manually deleting those data points? Lets get to it! Below you will find a quick breakdown on how I created my solution. The data goes from January 1st to January 10th in the year of 2013. We need to also add a trendline with a trend line formula and R value but this can be a manual step.

Make a boxplot in excel 2010 with outliers