SOLVED

# Formula REGR.LIN output problem

Copper Contributor

# Formula REGR.LIN output problem

Hi everyone,

I've a problem with the formula REGR.LIN .

In cell H6 I have wrote the formula as you can see in the pic that take the input data from the table on the left. Running the formula I should have 2 output data, the first one in cell H6 as it is in the pic and a second one in cell I6 that i cannot see (It doesn't seem to be "print", because as you can see in the second pic the formula actually gives me two outputs).

Can someone help me?

Best Regards

Luca

4 Replies

# Re: Formula REGR.LIN output problem

The REGR.LIN function in Excel returns an array containing two values: the slope and the y-intercept of the linear regression line. However, when you enter the formula directly into a single cell, Excel only displays the first value of the array, which is the slope, in that cell. The second value (y-intercept) is still calculated, but it's not displayed because the cell can only show one value at a time.

To display both the slope and the y-intercept in separate cells, you can use array formulas or split the REGR.LIN formula into two separate formulas.

Here's how you can maybe split the REGR.LIN formula to display both values (the function names are typically translated into Italian):

In cell H6 (for slope):

=INDICE(REG.LIN(F5:F12;E5:E12;VERO;FALSO);1)

In cell I6 (for y-intercept):

=INDICE(REG.LIN(F5:F12;E5:E12;VERO;FALSO);2)

And for the concatenated formula:

In cell H6:

="Pendenza: " & INDICE(REG.LIN(F5:F12;E5:E12;VERO;FALSO);1) & ", Intercetta: " & INDICE(REG.LIN(F5:F12;E5:E12;VERO;FALSO);2)

Ensure that semicolons (;) are used as argument separators in the formulas.

The text was created with the help of AI.

My answers are voluntary and without guarantee!

Was the answer useful? Mark as best response and like it!

This will help all forum participants.

# Re: Formula REGR.LIN output problem

Depends on which version of Excel you are. If on 2021 or 365 it shall show both value. If another one, enter formula as array one, i.e. use Ctrl+Shift+Enter instead of Enter.

# Re: Formula REGR.LIN output problem

I've got Office 2019,  I've tried to do what you suggested. But in the end I've got the same problem even if in the "formula bar" the formula is now between { }.

best response confirmed by lucavvf (Copper Contributor)
Solution

# Re: Formula REGR.LIN output problem

Sorry, forgot to mention. First you need to select both cells, after that enter the formula in formula in formula bar, next Ctrl+Shift+Enter.

Alternatively enter the formula into one cell, enter, select this and next cell, Ctrl+Shift+Enter

1 best response

Accepted Solutions
best response confirmed by lucavvf (Copper Contributor)
Solution

# Re: Formula REGR.LIN output problem

Sorry, forgot to mention. First you need to select both cells, after that enter the formula in formula in formula bar, next Ctrl+Shift+Enter.

Alternatively enter the formula into one cell, enter, select this and next cell, Ctrl+Shift+Enter