In response to a comment I have modified the LinestGap function to work on data arranged in rows. This function works the same as the Excel built in LinEst function, but ignores any data that has a blank cell in the x or y values. It is described in more detail at: Using LinEst() on data with gaps, and the revised spreadsheet (including full open source code) may be downloaded from: Linest-poly.xls
The screenshot below shows the revised function used to fit a quadratic curve to data arranged in rows:
The function in the edit box shows the X data specified in the form of an array function:
- =LINESTgap(B30:L30,B28:L28^{1;2})
This must be typed as shown, then entered into the spreadsheet by pressing Ctrl-shift-enter, when it will display as {=LINESTgap(B30:L30,B28:L28^{1;2})}.
It is important to note that when the data is arranged in rows the exponents for the x values must be entered as a column array, with a semi-colon separator: {1;2}. When the data is arranged in columns then this is entered with a comma: {1,2}.
Alternatively the x and x^2 values may be generated explicitly on the spreadsheet, as seen in rows 28 and 29, and then the function is entered as:
- =LINESTgap(B30:L30,B28:L29)
In this case the function will return the first result when entered as a normal function (with the Enter key), but to return all three results it must still be entered with Ctrl-shift-enter (see Using Array Formulas for more details).
The function works with data in rows by simply using the Worksheetfunction.Transpose function on both the X and Y data if the specified Y range has more columns than rows.
Newton Excel Bach, not (just) an Excel Blog 
Excellent stuff, Doug. I’d tried to use a transpose function in my attempt to convert your original code but got into a mess! Your solution is extremely neat. Well done and many thanks for your help. 🙂
LikeLike
thank you..have been looking for this for 3 hours now..finally!
LikeLike
I am using the same in Excel 2010 but it does not work. When I copy my data to the spreadsheet provided in the blog it works but when I create a new file in Excel 2010 and use the same function it does not work. Can you help?
LikeLike
Today’s post may help: https://newtonexcelbach.wordpress.com/2013/08/21/running-vba-routines-from-a-new-workbook/
Please let me know if that solves the problem.
LikeLike
Excellent stuff.
I modified it to ignore error values too since they are a pain to manually remove. ie I rewrote each of the if statements with the following lines
If (IsError(YA(i, 1))) Then
BlankRow = True
ElseIf YA(i, 1) = “” Then
and
If (IsError(XA(i, j))) Then
BlankRow = True
ElseIf XA(i, j) = “” Then
LikeLike
That’s the beauty of open source.
I’d be a bit cautious about giving code that ignores error values to anyone else though.
LikeLike
Hello,
Two queries
1) How to modify the function if I have gap in X values and not in Y?
2) Also the GAPs is X values that I have are coming from if function(related to some other condition), so the GAP actually is not a GAP but a formula like if(X=0,” “,X). But LinestGap does give a error?
Thank you
LikeLike
Does this modification for rows work with greater than 2 order polynomials? When I try, I get errors. Thank you!
LikeLike
Ben – can you give more details? The example starting at row 27 of the Data with Gaps sheet works with a cubic polynomial, and looking at the code I don’t see why it wouldn’t work with higher powers. The only requirement is that the Y range must also be in a row; i.e. the assumed orientation of both ranges is controlled by the Y range.
Also note that the easiest way to generate the x data for higher powers is to enter it as an array:
=LINESTgap(B31:L31,B28:L28^{1;2;3})
Finally, be careful of going to too high a level with the powers of x, see:
https://newtonexcelbach.wordpress.com/2011/02/04/fitting-high-order-polynomials/
LikeLike
Can LINESTGap be used to handle weighted least-squares regression?
LikeLike
You would have to apply the weights to the data on the spreadsheet, then use the function on the weighted data, or at least that’s the most obvious way.
You might also like to have a look at:
https://newtonexcelbach.wordpress.com/2014/09/12/excelpython2-alglib-and-spline-matrix-update/
and
https://newtonexcelbach.wordpress.com/2012/10/01/daily-download-14-curve-fitting-1/
These links provide more sophisticated least squares fitting routines that will certainly handle weighted regression, but I don’t recall how they work with data in rows or with gaps.
If you have any suggestions for new features, please let me know.
LikeLike