A comment on Using LINEST for non-linear curve fitting asked if it was possible to use the function on data with gaps. There are several options:
- Copy and sort the data to remove the gaps.
- Use the chart trendline function
- Use one of the on-sheet functions provided by Lori Miller in a reply to the comment.
- Use the LinEstgap() User Defined Function (UDF) that has been added to the Linest-poly spreadsheet.
A copy of linest-poly, including full open source code, may be downloaded from: LinEst-Poly.xls. Both the on-sheet functions and the LinEstGap() function must be entered as an array function, as described here: Using Array Formulas
Use of these options (other than number 1) is shown in the screen-shots below:
With continuous data all options give the same result:
Deleting some data, the LinEst function returns an error, but the other functions return a result with the rows with blank cells ignored. This result is consistent with the chart trend line result.
Using linest on filtered data (but with data in every row) includes the hidden rows in the analysis, as does the simpler of the two on-sheet functions. The other options use only the visible data:
The on-sheet formulas treat cells containing 0 (zero) as being blank, whereas the other options treat 0 as a valid data value: