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


  2. John Seed says:

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


  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?


  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


    If (IsError(XA(i, j))) Then
    BlankRow = True
    ElseIf XA(i, j) = “” Then


  5. Anks says:

    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


  6. Ben says:

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


    • 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:

      Finally, be careful of going to too high a level with the powers of x, see:


  7. Bill says:

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


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s