linest won't accept multiple reference ranges
 

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. Ive tried the standard form for referencing multiple cells (A4:D4,
F4) but linest wont 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.

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

Jerry

Alex Gardner wrote:

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. Ive tried the standard form for referencing multiple cells (A4:D4,
F4) but linest wont 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.

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" wrote:

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

Jerry

Alex Gardner wrote:

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. Ive tried the standard form for referencing multiple cells (A4:D4,
F4) but linest wont 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.

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

Jerry

Alex wrote:

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" wrote:


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

Jerry

Alex Gardner wrote:


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. Ive tried the standard form for referencing multiple cells (A4:D4,
F4) but linest wont 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.