Did you know that you can use a spreadsheet to do your math homework? Well, sometimes. One example of a situation where you can use a spreadsheet is for finding an equation that matches a set of points, also called "curve fitting".

The simplest form of curve fitting involves only two points. If you remember your geometry (or just think about it long enough), you know that the simplest equation to fit two points will be a line (although it doesn’t really look like a curve). For most people, this will be the most advanced curve fitting they ever learn to do by hand.

If you continue up in your math experiences to the level of linear algebra, you will find that larger numbers of points can be used to find the equations of more complicated curves. For example, three points may not fit on a line, but there is a second-degree polynomial that will match them exactly. Once you get to a higher number of points, such as ten points, you could match these points with a ninth-degree polynomial, but it is more practical to find a second- or third-degree polynomial to fit them.

In this article, I will show you how to do all of this graphically using Microsoft Excel.

Finding a Line with Two Points

This is a common problem for algebra students to deal with, including college algebra. You are given two points and are told to find the slope-intercept equation (y = mx + b) of the line that contains them. The first step is usually to find the slope, m. Then, the students will use a single point (either one of the two given) and that slope to find the y-intercept, b.

Let’s say that our points are (1,1) and (3,5). If you know how to find the slope-intercept equation for these two points, go ahead and try. To start out using a spreadsheet, we will set up a data table in Excel to define our points. I have labeled them “x” and “y” at the top.

Next, we will highlight the cells with our mouse. We will only select the cells with numbers and not the “x” and “y” labels we created.

With our cells still highlighted, we will move our mouse to the Chart Wizard icon and click it.

From there, we need to choose “XY (Scatter)” and click Next. Make sure “Columns” is selected (this is necessary because of how we made the data table) and click Finish. There are a lot of other things we skipped that could make our plot prettier, but we are just looking for the equation. At this point, we should have a plot appear in our spreadsheet that displays the points we chose.

We can see from our plot what the line fitting these points should look like. If we right-click one of the data points on the plot, we will have the option to “Add Trendline…”. Click to do so. Once we do this, we are given a choice of trendline types to use.

We wanted a line, so we will leave “Linear” selected. Next we will click on the Options tab and select to display the equation and the R-squared value.

Once we click OK, our plot will show all the new information.

Now we have a trendline, an equation for that trendline, and an R-squared value. If you figured out the slope-intercept equation at the beginning, you should have gotten the same one that is displayed on the plot now.

If you aren’t familiar with R-squared values, they have to do with the distance the points are from the line. It can take on values from 0 to 1, where 1 means there is a perfect match and means that all the points are on the line exactly.

We will now look at an example of a line that is used to match three points. There is no guarantee that there is a linear equation for three different points, but we an still get an approximation. This can be done by hand using some linear algebra (and is actually easier than you think, but much harder to understand), but can be done quite quickly using Excel.

We will start with the same points as before but we will add a third point: (2,2). Following the same procedure as before, we can create our plot.

Next we will add our trend line, making sure that we are using a linear trendline.

We still get an equation, but you will notice the line doesn’t match the points exactly. As you can clearly see, no line will touch all three of our points. This line is, however, the closest match to all the points. You will also notice that the R-squared value is no longer 1. It’s close, but it isn’t exactly 1 because the line doesn’t match perfectly.

In many practical situations, there are large amounts of data points that won’t match a line perfectly but we know there is supposed to be a linear relationship. The only reason why the data doesn’t appear linear in such cases is because ofm easurement errors. These are cases where a linear trendline should be used. If the relationship is not linear or is completely unknown, a polynomial (or some other type) should be used.

Using the same three points from before, we will now find a polynomial trendline. Since we have only three points, we only need to use a second-degree polynomial.

To start, click on the old trendline and delete it. Next, follow the same steps as before to add a trendline. The only difference is that we will now choose a second-degree polynomial instead of a linear trendline.

Now we got a perfect match with an R-squared value of 1 and a second-degree equation for our line.

In practical settings, the best fitting trendline is not always the best one. I tried many times in chemistry experiments during college to use the best fitting trendline to predict things such as pH measurements. In the real world, sometimes the less “pretty” line will result in a more accurate answer.

If you are going to use these methods for help on your math homework as I hinted at the start, they will give you great results! However, it will not show you where the results came from. As a result, it is only good for checking answers in most homework situations.