single function, multiple outputs...

[Adam Vogt]

I'm using the LINEST function, which in its simple mode
returns a slope and a y-intercept... how do I make the
selected cell return the y-intercept? Say my range is A1-
A10 and B1-B10... my formula reads

=LINEST(B1:B10,A1:A10)

I just don't know what to add to make it return the second
value (y-intercept) instead of the first (slope).

Thanks.

 

[Don Pistulka]

Adam,


=INTERCEPT(B1:B10,A1:A10)



Don Pistulka

 

[Adam V]

Thanks; that helps in this occasion, but is there a more
general answer on how to get each part from a function
that gives a multiple-part answer?

Adam

 

[dvt]

Adam V wrote...

Thanks; that helps in this occasion, but is there a more
general answer on how to get each part from a function
that gives a multiple-part answer?

You should be able to select a range of cells, enter the formula in one
cell, and array-enter the formula (ctrl-shift-enter) to see all of the
parts. I don't have Excel on this PC, and I can't give you specifics on the
LINEST function from memory. See the help on LINEST to see the appropriate
range size for the result.

Dave
dvt at psu dot edu

 

[Dana DeLouis]

Hello. Reading the help on Linest is a little confusing at first. Because
you have a simple X and Y data, the output from Linest will be 5 Rows, and 2
Columns.
You are only interested in the top row that has Slope and Intercept (which
Excel calls m1 & b)
Therefore, to extract these two items, you select two horizontal cells and
Array enter something like this...

=LINEST(B1:B4,A1:A4)

To get a vertical array with your slope and intercept, you transpose the
output.
Select two vertical cells and Array enter...

=TRANSPOSE(LINEST(B1:B4,A1:A4))

Suppose you wanted to find the R^2 value. There is an RSQ function, but
suppose we want to extract that from the Array output. From help, you will
notice that the r^2 value is in the 3rd row, first column. Therefore, you
can extract that value with the following. This is not Array entered.

=INDEX(LINEST(B1:B4,A1:A4,,TRUE),3,1)

HTH.

Note: There is also the two functions Slope & Intercept.