Positive and negative data in a linear regression

[moon miner]

Hello everyone,

I am trying to use LINEST function in order to estimate a linear regression. However, I can't make the function work when the Y variable has BOTH positive and negative values.

Say I have the following data:
View attachment 38604

LINEST works fine when all Y values are positive or when they are all negative, but it returns an error when I select both positive and negative data.

Does anyone know why this is and if there is any way I can work around it?

Thanks!

 

[AhoyNC]

Your above attachment doesn't work.
What error does your formula return?
Instead of using LINEST does it work if you use the regression tool in the DATA Analysis ToolPak? (see link below on how to add if it's not on your ribbon under Data - Data Analysis).
This link also shows some reasons you may get an error.

Incorrect output is returned when you use the Linear Regression (LINEST) function in Excel - Microsoft Support

Discusses a problem in which the incorrect output is returned when you use Linear Regression (LINEST) function in Excel.

support.microsoft.com