fbpx
blog

Quick Regression in Excel – The Little-Known REGLINP Formula

Joanna Grochowska

Learning Department Manager.

Graduated in Financial Mathematics and Computer Science and Econometrics from the University of Bialystok. Experienced tutor in mathematical and economic subjects. Associated with eTrapez since 2015.

She lives in Bialystok, Poland. She loves to travel and hike in the mountains. She enjoys meetings with friends, experiments in the kitchen and volleyball.


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 🙂

Bestsellers (Courses in polish only)

Kurs Mechanika - Statyka

Studia / Autor: mgr inż. Adam Kasprzak

39,00 

Kurs Mechanika - Kinematyka

Studia / Autor: mgr inż. Adam Kasprzak

39,00 

Kurs Statystyka

Studia / Autor: mgr Krystian Karczyński

39,00 

Kurs Matura Rozszerzona (Formuła 2023 i 2015)

Szkoła Średnia / Autor: mgr inż. Anna Zalewska

59,00 

Check all eTrapez Courses (polish)

Are you looking for college or high school math tutoring? Or maybe you need a course that will prepare you for the final exam?

We are "eTrapez" team. We teach mathematics in a clear, simple and very precise way - we will reach even the most knowledge-resistant students.

We have created video courses translated in an easy, understandable language, which can be downloaded to your computer, tablet or phone. You turn on the video, watch and listen, just like during private lessons. At any time of the day or night.

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).