There’s a difficult-to-Google, but extremely useful equation for linear regression called the Normal Equation. I used to have an implementation in VBA and somehow lost is. As it happened, I needed it today so I spent a little while recreating it.

This isn’t what I actually need the equation for, just a test case for making sure the equation works. If you want to try this out yourself, the data is the abalone data set from UCI. It’s a fairly simple data set which collates a whole load of data about abalone, along with the number of rings they have in their shells – an indicator of age, like tree rings.

The regression model I built is a simple one, with all the features as predictive variables and the ring count as the independent variable.

Here’s the code:

To use it, set up the data, set up named ranges for X, y and theta (theta should be a 1D array of length k). In the picture below, X is red, y is orange and theta is blue.

Use this code to call the function:

To use the results, the simplest way is to enter =ROUND(SUMPRODUCT(theta,C3:K3),0) in cell M3 and fill down to the bottom of the data.

There you have it, a really powerful linear regression function ready for anything you want to throw at it.

Jamie Bull | jamiebull1@gmail.com

Related Posts

WiGLE is a popular platform which can be used for finding the location of a device using the names of WiFi networks in its vicinity. I’ve written about this before, and wrote some Python code to interact with their API. This API has since been retired and replaced with a new one, as of December […]

Just a quick post to point out a couple of really useful tools.The first is a web-based tool for finding weather files for a location of interest. It’s similar to the Excel EPW finder tool we created a few years back, but much more modern looking. It is however missing a few of the useful […]

Eppy is a really useful library which I’ve written about several times, since before I really had anything to offer in terms of contributing code. Over the past year or so though, I’ve started to contribute back some of the changes and additions I’ve made while using eppy on academic and commercial projects.This post is […]

Leave a Reply

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