=LINEST help

[trdebruin]

Hello everyone!

I am currently trying to make an excel sheet for myself that will automatically calculate all the statistics and stuff when I enter my results. I use =LINEST to determine the slope and intersect of the calibration line. Now, the amount of data that I collect sometimes differs, so a lot of times, the inputs that =LINEST needs ( known Y's and known X's) include empty cells. This seems to really mess up the function, I've tried to enter 0's where there's no data. But that still changes all of the calculations because they're still considered data points. I was just wondering if anyone could help me with that. 

Thanks already :D

[#2]

There may be a number of ways to solve your problem. Without actually seeing your sheet, we're taking a stab in relative darkness. So if what I'm going to suggest doesn't work, then I'd strongly recommend you upload a sample of your worksheet, assuming you can do so without revealing any proprietary info.

What occurs to me is that you could use as a source for your LINEST function a named range. (Insert....Name...) and have an OFFSET function that designates the size of the range. You could most simply just have two cells that indicate horizontal and vertical ranges from the anchor cell (upper left corner)....

Now, if none of that makes any sense, please upload a sample of your spreadsheet.

[#3]

@mathetes 

I have uploaded the current version of the sheet :D

[#4]

@trdebruin 

Please check this discussion https://www.mrexcel.com/board/threads/blank-cells-and-linest.394619/

[#5]

@trdebruin 

Have a look at the attached sheet. It's your own with just a few modifications. Above the "Lijnschatting" table, I entered the text for the ranges you call for in the LINEST function. The reference will change based on the value in cell J24, which I interpreted as the number of rows to be included. The LINEST function will call for these ranges with the help of INDIRECT.

See if works for you.

[#6]

@trdebruin 

As predicted, there is more than one way to solve your problem. ;)

I'm going to assume that between Sergei and Riny you've gotten a fully functional (and flexible) solution.

[#7]

@Riny_van_Eekelen 

Your method seems to indeed achieve what I was trying. Could you, explain how this works?

I've taken a look at the formulas you added and I can't make much of it, so it would be nice if you could explain it to me. This way I will be able to replicate/change it by myself 

[#8]

@trdebruin 

In J24 you seem to have the number of rows in your data range for your line estimate. I.e. 5 rows in B8:C13.

In M23 I "calculate" the range for Y:

="C8"&":C"&8+J24

It simply creates a text by combining (using the &-sing) "C8", ":C" and  8+5. This becomes "C8:C13", being the range for Y. In N23, I do something similar to create the text that references to X (B8:B13). When you add something in B14 and C14, cell J24 becomes 6 and the ranges are automatically expanded to also include row 14. Thus, C8:C14 and B8:B14.

Within the LINEST function, I use INDIRECT. What this does is, look at cells M23 and N23 respectively to find the references to the ranges for Y and X. So, INDIRECT(M23) is the same as C8:C13. Hope this is clear. 

[#9]

@trdebruin 

This is not relevant to many at the moment because it uses modern dynamic arrays but it strikes me that FILTER would offer a solution to missing data.  I built an Excel Table 'D' with columns "X" and "Y" to hold the data.  The formula

= LINEST(D[Y], D[X])

errors if blank fields are present and gives inappropriate results if blanks are replaced by 0.

Defining named formulas 'X' and 'Y'

= FILTER( D[X], D[Y] )

= FILTER( D[Y], D[Y] )

allows the correct parameter to be determined using

= LINEST( Y, X )

[#10]

Thank you very much, I think be able to replicate this myself now