## Linestgap with data in rows

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.

This entry was posted in Excel, UDFs, VBA and tagged , , , , , , . Bookmark the permalink.

### 11 Responses to Linestgap with data in rows

1. Mike says:

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. 🙂

Like

2. John Seed says:

thank you..have been looking for this for 3 hours now..finally!

Like

3. M. A. Mian says:

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?

Like

• dougaj4 says:

Please let me know if that solves the problem.

Like

4. richard says:

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

Like

• dougaj4 says:

That’s the beauty of open source.

I’d be a bit cautious about giving code that ignores error values to anyone else though.

Like

5. Anks says:

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

Like

6. Ben says:

Does this modification for rows work with greater than 2 order polynomials? When I try, I get errors. Thank you!

Like

• dougaj4 says:

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/

Like

7. Bill says:

Can LINESTGap be used to handle weighted least-squares regression?

Like

• dougaj4 says:

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.

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.

Like

This site uses Akismet to reduce spam. Learn how your comment data is processed.