Using LINEST in Excel

today I'm going to show you how to do some quick and easy linear regression analysis with Excel the first way of doing this is with the data analysis toolpak that Microsoft provides for Windows users of Microsoft Excel I'm not going to show you how to use this you might be able to figure out on your own another thing that you might be able to use if you're using the Mac version which doesn't include the debt analysis toolpak is something called oat bran I'll post a link to this at some point so that you guys can can look at it and use it if you if you so desire but I think the easiest and fastest way is using the line st function and I'm going to show you how to do that today so I have some some X&Y data here and the first thing I'm going to do is select this data and generate a scatterplot now this is something that you're going to be doing for your lab reports you will need to clean this up quite a bit before actually including it in a lab report but for now I'm just going to show you how to add a trendline so we're going to add a trendline of a type of a linear type we're going to display the equation of the shot on the chart and display the r-squared value on the chart and so here you go so this is the linear regression analysis but you'll note a couple of things we don't have the error in the slope and the air and the intercept provided to us here and so we need those for a lot of the analysis that we're going to do in P chem lab and so we have to have a way of doing that now the easiest and fastest way that I found to do this is with the Lynas t function so what I'm going to do is I'm going to go over here and highlight this l hit equals li and EST parentheses open okay and this opens the function for us to start dealing with and here are the things that have to go into this function the first thing is is the Y values so I'm simply going to select those and hit a comma to move to the next thing which are the X values this third thing is the constant and basically this is a parameter for setting your intercept equal to zero we don't want to do that we want to let the data decide where the intercept is going to be so I'm going to leave that alone and basically I'll just put another comma in leaving it blank up here in the formula and that will set it to what it needs to be set to um we do want the statistics the statistics for the linear regression analysis so I'm going to set this value equal to true and then close the parentheses and hit enter now what you'll see is that this gives us the slope there's the slope one point six seven seven four same thing here okay now this formula minus T is an array formula which is maybe a little different than you've used before with Excel the way these work is that I'm actually going to select a large section here and this is comprised of two columns and four rows or five rows depending on how you look at it but this but this box here and then I have to actually select in the formula bar here and I'm going to hit command enter as a Mac user you would use control shift in her if you're using Windows and what this does is it automatically fills in all this data now here I've got a kind of a legend telling you exactly what each of these values represents the first is obviously the slope second is the intercept you can see it's the same as what we have from from in our chart um here we have the error and the slope and the air and the intercept so this is the air in the slope this is the air in the intercept here's our r-squared value just as it is on the chart here is the standard error and why it's not something that we're going to use a lot but it basically tells us what the error of Y is in the in the fitted values versus the experimental values not something that we use a lot but there it is here we also have the S statistic in the total degrees of freedom that we will use in this and this in this analysis okay and finally some sum of squares for the regression analysis and for the residuals you won't use these any of these things quite so much as you will be standard error in the slope and the standard error in the intercept these are the main values that we'll be using for our linear regression analysis and peak in lab so that's how you do it um if you need to do some kind of editing here we can actually see that if I change this value to 2.8 it will automatically update everything in this formula and so this is the simplest way that I know of to do linear regression analysis to get your standard error in your slope in your intercept have a good day Thanks

Channel: Jared Spencer

Views: 481,598

Keywords: Microsoft Excel (Software)

Id: 6wbcPbYbq6M

Length: 4min 29sec (269 seconds)

Published: Sun Feb 17 2013