Forum Discussion

trdebruin's avatar
trdebruin
Copper Contributor
Dec 05, 2019

=LINEST help

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 😄

excel
Formulas and Functions

9 Replies

Sort By
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Dec 12, 2019

    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 )

     

  • mathetes's avatar
    mathetes
    Silver Contributor
    Dec 05, 2019
    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.

Resources