Using LinEst() on data with gaps

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:

  1. Copy and sort the data to remove the gaps.
  2. Use the chart trendline function
  3. Use one of the on-sheet functions provided by Lori Miller in a reply to the comment.
  4. 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:

LinEst with continuous data

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.

Linest on data with gaps

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:

Linest with filtered data


The on-sheet formulas treat cells containing 0 (zero) as being blank, whereas the other options treat 0 as a valid data value:

LinEst including x value of zero

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

20 Responses to Using LinEst() on data with gaps

  1. Lori Miller says:

    Doug – The formula is just using the fact that regression coefficients are unchanged by inserting rows of zeroes into the regressor matrix. Good spot on the handling of 0’s, I think a simplified version that also accounts for this is:

    (adding 1E-99 avoids 0^0, IFERROR could be used instead). I also like the UDF, perhaps one could include a generic function as well so that other functions like Alglib could work with gap data. Something like:

    PS I’m not sure the latest version of the file is linked so perhaps the revised version be included instead? Thanks.


    • dougaj4 says:

      PS I’m not sure the latest version of the file is linked so perhaps the revised version be included instead? Thanks

      Damned case-sensitive Unix file servers!

      I’ve linked to the right one now – thanks for letting me know.


    • Ed Pink says:

      Hi Lori,

      your simplified version looks great, i copied the 1E-99 version and confirmed that it worked… but i played around a bit with ISERROR and couldnt get it to work (it was always gives the same result as the other on-sheet functions, ignoring the X_4 = 0 data points). How would the complete formula look with ISERROR ?

      what i tried:



      • Lori Miller says:

        Ed – try with: IFERROR((X_4)^{0,1,2},1). i.e. set 0^0 = 1. A related formula for a weighted regression is here:


      • Ed Pink says:

        Hallo Lori
        oh yeah.. thats right, 0^0=1 …. woops. guess im gonna go back to school.. i knew when i burnt my books after finishing high school that it would come back to bite me one day!

        Thanks a heap ! Ed


      • Lori Miller says:

        0^0 is actually an interesting topic in itself. In high school it is taken to be indeterminate since 0^x and x^0 give different values as x->0. Setting the value equal to 1 is a common convention in further study and simplifies expressions like the binomial theorem but most computer compilers throw an error for this expression. Much more discussion can be found in online forums.


  2. dougaj4 says:

    Lori – good idea on the generic gap function – I’ll put it on th e”to do” list🙂


  3. jp says:

    Doug … Hi … Great posts/comments on LINEST/LOGEST … just wondering if I can quickly adapt the LINEST GAP function for exponential curves? …. cheers, James


  4. dougaj4 says:

    Hi James – I’ll need to remind myself of the details. I’ll try and have a look at it over the weekend (if I don’t get distracted by other things :))


  5. Mike says:

    Doug, I tried to modify your UDF code to suit the case that the arrays are in rows rather than columns….but unsuccessfully. It’s for a second order polynomial. Can you help?
    Thanks in advance…


  6. Pingback: Linestgap with data in rows | Newton Excel Bach, not (just) an Excel Blog

  7. Pingback: Daily Download 14: Curve Fitting 1 | Newton Excel Bach, not (just) an Excel Blog

  8. Yux Yux says:

    Thankful for this post and the comments. Helped me get past my little obstacle.
    Just thinking about the problem of “The on-sheet formulas treat cells containing 0 (zero) as being blank, whereas the other options treat 0 as a valid data value:”
    Can’t combining onsheet formula 2 with Lori’s zero value solution can get one past that little issue?:
    =LINEST( SUBTOTAL(3,OFFSET(X_4,ROW(X_4)-MIN(ROW(X_4)),,1)) *Y_4, IF(X_4″”,SUBTOTAL(3,OFFSET(Y_4,ROW(Y_4)-MIN(ROW(Y_4)),,1))*(X_4+1E-99)^{0,1,2},0),0)


  9. Yux Yux says:

    That was supposed to be:
    =LINEST( SUBTOTAL(3,OFFSET(X_4,ROW(X_4)-MIN(ROW(X_4)),,1)) *Y_4,


  10. Yux Yux says:

    One more try with the “Not Equal To” sign after IF(X_4 that is not supposed to be tags…

    =LINEST( SUBTOTAL(3,OFFSET(X_4,ROW(X_4)-MIN(ROW(X_4)),,1)) *Y_4,
    IF(X_4 < > “”,SUBTOTAL(3,OFFSET(Y_4,ROW(Y_4)-MIN(ROW(Y_4)),,1))*(X_4+1E-99)^{0,1,2},0),0)


  11. Aleks says:


    This code is exactly what I’ve been looking for. I am trying to get the Linestgap function in a spreadsheet I am working on. Move or copy -> create copy in my current worksheet doesn’t work. Could someone please advise?

    Many Thanks,



  12. Aleks says:


    Can anyone give examples of using linest gap for the other common functions?

    How can LinEstGap be used to determine r-squared values?



  13. Rod says:

    Many thanks for the code. It works wonderfully and has saved me a lot of time in spreadsheeting processing.

    Just a quick question…if instead of blanks cells I had #NA, what would have to change in the code for it to be used ?


    • dougaj4 says:

      It actually allows that already. If you look at the examples on the Data with gaps sheet, change cell D34 to:
      You will be able to change some of the data to =NA() (or another error function), and that data will be ignored, rather than returning an error.
      If you open the function in the function wizard, you will see that the last argument is “IgnoreErrors”. The default is False.


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