polynomial coefficients using linest when there are #N/A in the Y data set

I have a data set with fixed X values and corresponding Y values that I can feed in via a formula. There is not necessarily a Y value for each X value. in the cells with no Y data point, a #N/A populates. I can create a scatter plot and fit a polynomial to the data on the chart and it produces the coefficients of the displayed equation. My problem is that I can not get the same coefficients using the linest formula unless I remove all of the data points where the Y values are #N/A. Obviously this is a solution, but each time i pass new Y data into the table it will populate differently, so I need to keep the X values locked in place and not delete them. It seems that if a chart can ignore the #N/A data then the formula might be able to do the same.

I attached the file with the problem for you to look at.

Hello barrfly,

I am moving your post to the Charting forum. Andy Pope is our Charting guru and help you better with this problem than I can.

You could use AdvancedFilter to copy the relevant values to another sheet, and perform the regression there.

I know that is an option, but I am using this for dozens of data sets daily and want to avoid this step. Aside from coding this step in VBA, it would not be practical to filter each data set manually, which is why i am looking to see if there is a way to do it formulaicly.

Again, if the charting tool in excel can ignore #N/A in the data set, there must be a formulaic solution too.

Thanks for the response and i hope to see more feedback on this topic.

"unless I remove all of the data points where the Y values are #N/A. Obviously this is a solution, but each time i pass new Y data into the table it will populate differently, so I need to keep the X values locked in place and not delete them. It seems that if a chart can ignore the #N/A data then the formula might be able to do the same."

As shg pointed out in a similar thread, chart trendlines are calculated using a different method to that of the worksheet formula LINEST.

The chart handles #N/A the formula does not.

The only other approach is to use code to parse the trendline formula text.

As a starting point for parsing the formula, you could use this, after formatting the trendline formula for scientific format with lots of decimals.

Please Login or Register  to view this content.

I claim no credit for this -- it probably came from Andy, but perhaps elsewhere.

If you're using VBA to parse further (rather than formulas), delete the Application.Volatile.