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 a...
excel
Formulas and Functions
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.
    • mathetes's avatar
      mathetes
      Silver Contributor
      Dec 06, 2019

      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.

    • Riny_van_Eekelen's avatar
      Riny_van_Eekelen
      Platinum Contributor
      Dec 06, 2019

      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.

      • trdebruin's avatar
        trdebruin
        Copper Contributor
        Dec 12, 2019

        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 

         

Resources