EXCEL TRENDLINE SLOPE R2 HOW TO
In addition to showing how to apply LINEST for a polynomial, the article also shows how to apply it for other data fits including logarithmic, powers, and exponentials.Ĭourtesy of the article’s author, I learned that for the polynomial fit, to get the coefficients you need, you use the following form of the function: I would have never figured it out from the Excel tutorial information. Then I came across a LINESTarticle on the web that opened the door to my understanding.
Plus, it was not clear to me how to apply it for the valve C Vsituation. It is one of the statistical functions, and when I read through the discussion at the Excel tutorial link I reference above, I was a bit overwhelmed by the math jargon. It can also force the y intercept to be zero and give you all of the statistical data about the line (like the r 2values, etc.) In general terms, it is a least squares curve fitting technique where you input your y and x values and the function returns the coefficients for the equation for your line. It turns out that there is an Excel function called LINEST, which is what you can use to do this. My reasoning was that Excel must know them otherwise it could not have drawn the trend line that visually showed a much closer fit.
That got me curious about how you would actually get more accurate numbers for the coefficients out of Excel. But when that did not prove to be the case, I realized that with the high power polynomials (x to the 5 thfor instance) even small change in the coefficient would make a big change in the result and that the problem was probably related to the rounding off of the coefficients. Initially, of course, I thought I had miss-entered one of the coefficients. So, I then wrote a formula using the coefficients in the trend line equation and got this result when I plotted it to check myself. Visually, the trend line looked like a pretty good fit with the 5 thorder polynomial. So, I plotted my curve and got this as a result. Then, I got to thinking that if I could do a curve fit, I could use the equation for the curve to solve for the C V not a big time saver for picking a particular valve, but if I saved the spreadsheet as a tool or wanted to play “what if” games, it could be handy.
EXCEL TRENDLINE SLOPE R2 SERIES
I was basing my selection of a Bray series 30 butterfly valve and had the data for its flow coefficient (a.k.a C V)at different disc angles and decided to make a graph so I could just read the C Vfor angles that were not directly documented. So, I was looking at the valve performance for my selection at different flow rates. How I got into this was that I was working on a control valve selection for a condenser water system that has a number of different operating flow rates. But in figuring out how to work around it, I learned some things that will probably be useful, so I thought I would share them. Most of you probably already realize this, and when I noticed the issue, I sort of had a hunch about the reason for it. And for some applications, the digits that were dropped could make the difference between making an accurate prediction from your data and one that was not so good, especially if you multiply them by numbers that have big exponents. In other words, the coefficients presented in the equation are correct, but rounded off. But, in some cases, especially with high power polynomials, your predictions could be way off if you did that because of the compounding of rounding errors. It could be pretty tempting to write a formula that used the trend line equation and assume it was correct. you need to be careful if you use the equation to predict data, especially with higher order polynomials. and have Excel put the equation for the line on the graph ….
… experiment with the options to find something that is a reasonable fit …. The bottom line is that if you use Excel’s trend line feature to apply a trend line to a set of data in a graph …. I realized something the other day while doing a curve fit in Excel that I figured was worth sharing.
0 kommentar(er)
