Okay, here's one I've been trying to work for some time, with LINEST.
Overview:
I have seven numbers in "A" column (x) and seven in "B"
. In "C", I display a "YES" if the value in "B" is less than or equal to 10, and a "NO" if the value in "B" is greater than 10. This is to attempt to use the linest array to find the slope and y-intercept of the curve fit. The idea is to eliminate the y-values with a "NO" next to them (greater than 10), and the corresponding x-values that make up the curve, and then find slope and intercept. Here is what I have so far:
I get some values, but they are not the slope and y-intercept of the curve. So I thought another attempt would do it.
Second idea:
I took the "NO" points, which I considered invalid for the curve, and reassigned an #N/A value to the x and y-value for that point. When I chart this data, and highlight all cells, good and bad, the chart is able to throw out the #N/A points, and still produce a curve with a correct trendline, and equation.
I thought this would work for LINEST as well. However, let's say I moved the "redone" points to "D" and "E". Then I just tried LINEST again like so:
Now I get #VALUE! for the array, so long as there are any #N/A points in the curve data.
Question:
How in the world do I get Excel to take an x-y data set, thrown out the points with y-values greater than 10, and still give me the correct slope, y-intercept, and correlation coefficient with the remaining "acceptable" points? I can get the chart to understand, but convincing LINEST is quite another issue! Your help is greatly appreciated.
Dustin
Overview:
I have seven numbers in "A" column (x) and seven in "B"
. In "C", I display a "YES" if the value in "B" is less than or equal to 10, and a "NO" if the value in "B" is greater than 10. This is to attempt to use the linest array to find the slope and y-intercept of the curve fit. The idea is to eliminate the y-values with a "NO" next to them (greater than 10), and the corresponding x-values that make up the curve, and then find slope and intercept. Here is what I have so far:
Code:
{=LINEST(IF(C1:C7="YES",B1:B7,A1:A7))}Second idea:
I took the "NO" points, which I considered invalid for the curve, and reassigned an #N/A value to the x and y-value for that point. When I chart this data, and highlight all cells, good and bad, the chart is able to throw out the #N/A points, and still produce a curve with a correct trendline, and equation.
I thought this would work for LINEST as well. However, let's say I moved the "redone" points to "D" and "E". Then I just tried LINEST again like so:
Code:
{=LINEST(E1:E7,D1:D7,TRUE,TRUE)}Now I get #VALUE! for the array, so long as there are any #N/A points in the curve data.
Question:
How in the world do I get Excel to take an x-y data set, thrown out the points with y-values greater than 10, and still give me the correct slope, y-intercept, and correlation coefficient with the remaining "acceptable" points? I can get the chart to understand, but convincing LINEST is quite another issue! Your help is greatly appreciated.
Dustin
