Quick Regression in Excel – The Little-Known REGLINP Formula

Picture of Joanna Grochowska-Angielczyk

Joanna Grochowska-Angielczyk

One of the super important steps in the econometric modeling process is estimating its parameters. In my Econometrics Course, I dedicated the whole Lesson 3 to this topic.

In the lesson, I showed how to manually calculate the structural coefficients of the model. This was done using direct formulas (you know, the ones with sums) in simple regression, and the matrix method in multiple regression. All of this, of course, was using the Least Squares Method.

However, very often in classes or when working on various econometric projects, MS Excel is used for calculations. There, with the “REGRESSION” function, you can quickly and easily get almost all the necessary results for estimation and later verification of the model. That’s also something I covered in my Course.

In this article, I’d like to show you another additional function used in Excel. It’s the REGLINP() formula.

I have such an example econometric model, describing the relationship of a house’s value based on four explanatory variables:

Y – value of the house [in thousands of PLN]

X subscript 1 – square footage [in m squared]

X subscript 2 – floor number

X subscript 3 – annual income of the buyer [in thousands of PLN]

X subscript 4 – distance from a service center/store, etc. [in m]

Here are the precise details:

So, I’m going to estimate the model parameters:

Y space equals space alpha subscript 0 space plus alpha subscript 1 X subscript 1 space plus space alpha subscript 2 X subscript 2 space plus space alpha subscript 3 X subscript 3 space plus space alpha subscript 4 X subscript 4 space plus space epsilon.

I’m going to use the REGLINP() function for this.

This function is an array function. So, before you select it, I mean, before you insert the REGLINP() function, you’ve got to prepare a space for the result, meaning select as many cells in the ROW as there are model parameters. In our case, that would be 5 cells (from alpha subscript 0 to alpha subscript 4).

Now select from the menu under FORMULAS –> INSERT FUNCTION. Then, in the pop-up that appears, look for the REGLINP function.

After hitting OK, a window like this should pop up for you to fill out:

Alright, let’s fill this out.

“Known_y” is the range of numerical values for the dependent variable Y. Meanwhile, “Known_x” is the range for all the explanatory variables X subscript i. Just place your cursor in the cell in the window and then select the range of all relevant numbers from the data table.

Next up, we fill in the last two formulas. First up, “constant”. You’ve gotta enter one of two values here – 0 or 1. It depends on whether our model includes a constant, meaning if it has an intercept or not. In our case, there’s an intercept, so I’m putting “1”.

The last cell to fill: “statistics”. Just like above, you can enter two logical values: 0 (false) – if you just want the coefficient values for estimation, or 1 (true) – if you want additional statistical values of the econometric model to be displayed (like standard errors, the coefficient of determination, etc.)

Now it would be a good time to click OK and wait for the result to show up.

But wait a sec! After clicking “OK”, indeed, the result will show up, but only in the first cell… What about the rest?

Remember, with array formulas in Excel, to get the final result in all cells, you can’t just click OK; you need to use the keyboard shortcut:

<CTRL>+<SHIFT>+<ENTER>.

Only then will our final and correct result appear:

And another crucial tip – the estimated parameter values are read BACKWARDS. Yeah, someone programmed it that way 🙂

So, our estimated model looks something like this:

Y with hat on top space equals space minus 7 comma 7969 plus 3 comma 2114 X subscript 1 space minus 0 comma 8855 X subscript 2 plus 0 comma 8168 X subscript 3 plus 0 comma 0305 X subscript 4.

Hope you dig this extra option in Excel. Of course, I invite you to my course, where you’ll find out what to do next with such an estimated model 🙂

konometria jest dosyć młodą dziedziną wypływającą z ekonomii i matematyki. W praktyce, dzięki modelom ekonometrycznym, możesz „zmierzyć gospodarkę”.Polega to konkretnie na zmierzeniu, jak zachowuje się jedna zmienna w zależności od innych. I na podstawie analizy tego, co było, możesz określać, co będzie się działo w przyszłości.

Wykorzystasz do tego przeróżne obliczenia, testy, schematy. Jedne będą bardzo proste, inne trudniejsze. Jednak najczęściej będzie się liczyło nie to, jak dojdziesz do wyniku, ale jak go zinterpretujesz, odczytasz i jakie wnioski wyciągniesz.

Poniższe Wykłady dotykają najważniejszych pojęć teoretycznych. Jestem przekonana, że pomogę Ci odkrywaniu tego, czym jest ekonometria. I przy okazji uda Ci się zaliczyć ten przedmiot na studiach.

Leave a Reply

Your email address will not be published. Required fields are marked *

Your comment will be publicly visible on our website along with the above signature. You can change or delete your comment at any time. The administrator of personal data provided in this form is eTrapez Usługi Edukacyjne E-Learning Krystian Karczyński. The principles of data processing and your related rights are described in our Privace Policy (polish).


Kategorie

Wirtualny nauczyciel AI działający w przeglądarce internetowej.