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.
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |