![]() ![]() LinEstGap = WorksheetFunction.LinEst(YA2, XA2, Const0, Stats) IErr = RemoveGaps(Ycells, XA, XA2, YA2, Const0, Stats, IgnoreErrors) I have extracted the code for extracting blanks and errors into a separate function (RemoveGaps), so the LinEstGap function is now reduced to:įunction LinEstGap(Ycells As Range, XA As Variant, Optional Const0 As Boolean = True, Optional Stats As Boolean = False, _ Linest-PolyA has fixed size array functions that will work with older versions of Excel (see Using Array Formulas for more details). Linest-PolyDA uses the new dynamic array feature. The download zip file contains two versions of the spreadsheet. ![]() The revised spreadsheet includes examples and full open-source code, and can be downloaded from: Linest-PolyDA.zip ![]() It also optionally allows any lines with Error values to be ignored, rather than returning an error.įollowing a comment here I have modified the code and added additional functions to work with all the non-linear curve types provided by the chart trend line (Exponential, Logarithmic, Polynomial, and Power). The LinEstGap user defined function (UDF) allows the built-in Linest function to be used on data with empty cells or hidden rows, returning results that agree with the chart trend-line function, which automatically ignores blank cells and hidden data. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
March 2023
Categories |