I'm fairly sure this funcrfun is unavailable but if someone could confirm or point my down another path I'd appreciate it.
I
I'd love to be able to pull the Y='s into a cell where I can reference "X" to another cell. It's quite time consuming manually typing each equation. Is there a way to do this to help with constant changing data sets?
Thanks
MacBook Pro
You cannot extract it from the chart but you can accomplish it another way:
For linear fit, you can use LINEST or SLOPE and INTERCEPT in your table to create the coefficients or use FORECAST to get Y values for any X values. You can use LINEST to find coefficients for polynomial fits as well. And, with some math, you can do other fits, also using LINEST. Here is an example of a polynomial fit.
You could do it all in the one table but I used one for the chart and one for the math. The formula in Table 2 cell E2 is =INDEX(LINEST(C,A:B),1)
The formulas in E3 and E4 are the same but with the index numbers 2 and 3 instead.
You cannot extract it from the chart but you can accomplish it another way:
For linear fit, you can use LINEST or SLOPE and INTERCEPT in your table to create the coefficients or use FORECAST to get Y values for any X values. You can use LINEST to find coefficients for polynomial fits as well. And, with some math, you can do other fits, also using LINEST. Here is an example of a polynomial fit.
You could do it all in the one table but I used one for the chart and one for the math. The formula in Table 2 cell E2 is =INDEX(LINEST(C,A:B),1)
The formulas in E3 and E4 are the same but with the index numbers 2 and 3 instead.
You can read about LINEST on the internet to see all it can do. It is the same as in Excel. You need to turn on the "more stats". I think this is it but check it against the chart trend line just to be sure.
=INDEX(LINEST(C,A:B,1,1),3,1)
I do not know what you mean by "uneven data". If you mean they require an even number of data points, they do not.
LINEST is a function that creates a linear best fit of your data. Unlike most functions that result in a single value, LINEST provides an array of values and you need to use INDEX to get to each one.
I will take some time to digest and play with those functions. The problem I'm running into is that a chart will calculate uneven data sets and leave out the extra data, those formulas require even data sets as far as I can understand. The size of each data set will change with each set of companies I'm comparing. I'm sure I can do it, probably just have to build a few more tables. Anyway, thanks for giving me something to work off of.
This helps a lot, thanks. Now, how would I return that r squared? I know for linear relationships you can use the correl function, but how would I return the r-squared for the polynomial relationship. Thanks
Sorry for the typos, on my phone with fat thumbs.
How to extract the Y=equation from a chart
