When using a spreadsheet or any kind of scientific software, you often find the possibility to do a linear regression, this allows you to find the line that fits the best a cloud of point. To do so softwares use what is known as the method of the least squares. Unfortunately spreadsheets are only capable of doing this method for a 2D space.
In this article I will attempt to explain how we can use a spreadsheet to to find the least squares plane from a cloud of point in a 3D space. And hopefully you will have a better understanding of this method.
First of all let’s consider our set of point. Each point has three coordinates, x, y and z where x, y, z are three reals. You can see the set of point use in this exemple in the following picture.
Now that we have our points we can start digging into the math. Let me remind you what a plan is mathematicaly speaking. A plan is the result of a linear fonction of two variables, these fancy words simply means that for a couple of two numbers _x _and y, the function gives you a third number z, thus the link between x, y and z is linear, meaning the operations done on x and y are only multiplication with a real or addition with a real. Finally we can write this equation for a plan P’ :
Any given couple of _x _and y has a corresponding z value, we can use our x and y values in this equation to express the gap between the z value of our point and the z value it should have if it was part of this “ideal” plan. We will call this new function g for gap.
#numbers #mathematics #excel #least-squares #spreadsheets #cloud
