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:
- Copy and sort the data to remove the gaps.
- Use the chart trendline function
- Use one of the on-sheet functions provided by Lori Miller in a reply to the comment.
- 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
Newton Excel Bach, not (just) an Excel Blog 
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:
=LINEST(1*Y,ISNUMBER(Y)*(X+1E-99)^{0,1,2},0)
(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:
=LINEST(Gap(Y,X),Gap(X,Y)^{1,2))
PS I’m not sure the latest version of the file is linked so perhaps the revised version be included instead? Thanks.
LikeLike
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.
LikeLike
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:
=LINEST(1*Y_4,ISNUMBER(Y_4)*ISERROR((X_4)^{0,1,2},0);0)
Thanks
Ed
LikeLike
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: http://stackoverflow.com/questions/11087773/weighted-trendline
LikeLike
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
LikeLike
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.
LikeLike
Lori – good idea on the generic gap function – I’ll put it on th e”to do” list 🙂
LikeLike
Doug … Hi … Great posts/comments on LINEST/LOGEST … just wondering if I can quickly adapt the LINEST GAP function for exponential curves? …. cheers, James
LikeLike
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 :))
LikeLike
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…
LikeLike
Pingback: Linestgap with data in rows | Newton Excel Bach, not (just) an Excel Blog
Mike, see today’s post:
https://newtonexcelbach.wordpress.com/2011/11/26/linestgap-with-data-in-rows/
LikeLike
Pingback: Daily Download 14: Curve Fitting 1 | Newton Excel Bach, not (just) an Excel Blog
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)
LikeLike
That was supposed to be:
=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)
LikeLike
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)
LikeLike
Hi,
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,
Aleks.
LikeLike
Also,
Can anyone give examples of using linest gap for the other common functions?
How can LinEstGap be used to determine r-squared values?
Cheers!
LikeLike
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 ?
LikeLike
It actually allows that already. If you look at the examples on the Data with gaps sheet, change cell D34 to:
=LINESTgap(B31:L31,B28:L28^{1;2;3},,,TRUE)
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.
LikeLike
Hello,
I would like to transpose the Linestgap-function from cells D20:F20 to cells G20:G22.
Although whatever i try, i do not succeed. Is there a way to do this?
It seems it is due to the array-format of the formula. Although there should be a method to enter the formula vertically instead of horizontally i suppose?
Many thanks!
Mike
LikeLike
You can wrap the function with the Transpose function:
=TRANSPOSE(LINESTgap(C4:C14,A4:A14^{1,2}))
Enter the function as above, then select three rows in a single column (with the function In the top row), press F2, then Ctrl-Shift-Enter
LikeLiked by 1 person
Thanks a lot dougaj4, this works!
LikeLike
Hello,
I would like to run a multiple (!) factor analysis through linest, again excluding all rows that contain zeros (or if that’s easier blank cells). Is there a way I can do this using the above shown linest functions?
FYI – my Y variables are located at C15:C26, my X1 variables are located at D15:D26, and my X2 variables are located at E15:E26.
Thanks in advance!
Julian
LikeLike
The example shown (and in the download file) has the x data in two columns. In the example the second column is the square of the first column, so it fits a quadratic function to the data, but it works the same if the two columns are independent variables, in which case you get the coefficients for y = ax1 + bx2 + c.
Also the x range can be as many columns as you want. It’s not limited to one or two.
LikeLike
Thank you, fantastic job..
LikeLike
Is it possible to use the LinestGap function for a power equation? I am using the following formulas to calculate the a and b values for a power equation: a=EXP(INDEX(LINEST(LN(B2:B329);LN(A2:A329);;);1;2))
b=LINEST(LN(B2:B329);LN(A2:A329);;)
I apply filters to the data to show #N/A if the data is above or below certain data. I would however like to automatically see how the constants for the equation changes as I change the filter, so I don’t want to manually sort out the #n/a data.
LikeLike
Linestgap doesn’t work at the moment with the input as you have set up for Linest, because the X and Y ranges have to be ranges of numbers, without the LN(), but if you set up a couple of helper columns it will work. If Col C Is =LN(A2) and Col D is = LN(B2) (both copied down to row 329), then:
=LINESTGAP(D2:D329,C2:C329,,,TRUE)
will return b and LN(a).
Note that the third optional argument (IgnoreErrors) must be set to True if you want to treat #N/A as an empty cell, the default is False.
It would be quite easy to set up another VBA function (say PowerGap) to find the LN values of the input data, and also do the Exp(b) on the result, so you could just enter:
=POWERGAP(B2:B329,A2:A329)
and get the a and b factors returned in an array. I will have a look at that and post something in a few days.
LikeLike
See:
LinEstGap with non-linear functions
https://newtonexcelbach.com/2020/03/14/linestgap-with-non-linear-functions/
LikeLike
Thank you so much! You have just made my life significantly easier!!
Is there any way to get the R squared value?
LikeLike
Enter TRUE as the 6th argument:
=PolyEstGap(Y_1,X_1,6,,TRUE)
The function returns a 5 row array, with R2 the first item in the 3rd row.
See Linest help for details:
https://support.office.com/en-us/article/linest-function-84d7d0d9-6e50-4101-977a-fa7abf772b6d?NS=EXCEL&Version=90&SysLcid=1033&UiLcid=1033&AppVer=ZXL900&HelpId=xlmain11.chm60097&ui=en-US&rs=en-US&ad=US
LikeLike
Pingback: LinEstGap with non-linear functions | Newton Excel Bach, not (just) an Excel Blog
Hello, So’ I’ve been playing arround with the Workbook in the sheet with gaps,, and found that If I mess around erasing random values, and I’ve found that If, for example, I erase a value in cell A11 and B13, the only function that works is the UDF. Is there a way to make the Function 1) or Function 2) to work, so as to make the function work with independent variables?
LikeLike
Hi Eduardo – I don’t get that. Could you send a copy of your spreadsheet to my gmail account (dougaj4) and let me know your Excel version.
LikeLike
Dear Doug, I really love your workbook and was modifying function 1) to ignore certain values in the regression (=LINEST(ISNUMBER(X_4)*Y_4,IF(X_40,IF(Y_4>M1,IF(Y_4<N1,ISNUMBER(Y_4)))*X_4^{0,1,2}),0)) . It is working perfectly fine but I have a stupid question: why ^{0,1,2}. I don't get the 0 but iI guess it is part of the trick. It would be awesome if you could explain. Best wishes, Andreas
LikeLike
Any number (other than zero) raised to the power zero = 1, so the X^0 part generates the constant term in the polynomial equation.
Does that make sense?
LikeLike
Dear Doug, I have been using the UDF LINESTGap for a few weeks now, and wish I had found it sooner! It’s saved me a lot of time, thanks.
One related question from me, do you have any plans (or past experience) in using LINEST with errors in both y and x? (Background covered in eg. https://en.wikipedia.org/wiki/Errors-in-variables_models). My experience in fitting models to test data on materials is that often there are errors in both y and x that need to be considered, especially as the data approach asymptotes.
Any thoughts would be appreciated.
LikeLike
Hi Chris, Thanks for the comment and the link. That’s not something I have looked at but from a quick read of the Wikipedia it looks like it makes sense to do things differently in the case with errors in x and y.
I’ll add it to my (lengthy) list of things to look at.
LikeLike
Hi Doug, simply to add that for many practical cases the approach of Deming would most likely suffice, and could be a useful starting point (https://en.wikipedia.org/wiki/Deming_regression).
LikeLike
A search on Deming regression in Excel came up with:
https://peltiertech.com/deming-regression-utility/
which I have downloaded but not looked at yet.
LikeLike