linest won't accept multiple reference ranges

[Alex Gardner]

I have as extensive data set and some data is missing in places, I want to
use linest and reference my X and Y variables to multiple non-connecting
cells. I’ve tried the standard form for referencing multiple cells (A44,
F4) but linest won’t recognize the reference. Does any one have any
suggestions?

Example of data set

X Y1 Y2 Y3 Y4 Y5 ……….
1 2 2 3 6 3
2 1 N/A 2 3 1
3 1 2 4 1 2
4 N/A 1 5 3 1
5 N/A 3 5 2 N/A
6 2 3 6 1 4
..
..
I want slope of each data set.

 

[Jerry W. Lewis]

Use SLOPE instead of LINEST. SLOPE will ignore empty cells and cells
that contain text or boolean values.

Jerry

 

[Alex]

Thanks for the help Jerry. Any idea how to find the R-Squared value of a
similar dataset. I'm having the same problem as I did with the linest
function.

 

[Jerry W. Lewis]

For simple linear regression, you could use RSQ() or CORREL()^2

Jerry