single function, multiple outputs...

  • Thread starter Thread starter Adam Vogt
  • Start date Start date
A

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.
 
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
 
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?
Click to expand...

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
 
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.
 
Back
Top