Linest question

[stuartcm]

I am looking to report back the regression coefficients for a 2nd order polynomial fitted to a set of data.

There is one X variable, and one Y variable, but there are inputs on three different worksheets that I want combined.

How do I input into the formula the range of Xs and Ys when they are over 3 worksheets?

Thank you in advance for any help

 

[sijpie]

If the input cells over the three sheets are all in the same cells, such as Sheet1!B12, Sheet2!B12 and Sheet3!B12 then you can use
Sheet1:Sheet2!B12

for instance =SUM(Sheet1:Sheet2!B12) will give the sum of those cells.

Is that what you need?

 

[stuartcm]

Hello,

Thank you for the reply.

The 3d reference function (as you describe) does not work with the LINEST function. I think it's more because (from what I can see) the LINEST function will only work with one range for the Y variable.

Is this the case?

 

[sijpie]

I assume what you say is correct (I haven't experimented with it).

So then you would want to map the 3d range onto your spreadsheet. If the range is constant then that of course is easy to set up. If the range changes, you could write a little macro to do it for you.

 

[stuartcm]

Hi,

Thank you for the continuing help. Unfortunately I'm working in Excel 2008 for the Mac so macros are out of the question!!!!!!

The ranges do change between sheets, so I'm now copying and pasting throughout the whole selection of sheets