# Excel "intercept/slope" and "forecast" - funny math?

**URL:** <https://boards.straightdope.com/t/excel-intercept-slope-and-forecast-funny-math/658263>\
**Category:** Factual Questions\
**Created:** [May 13, 2013, 9:16pm UTC](https://boards.straightdope.com/t/excel-intercept-slope-and-forecast-funny-math/658263 "2013-05-13T21:16:27Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![KCB615](https://avatars.discourse-cdn.com/v4/letter/k/ea666f/32.png) [@KCB615](https://boards.straightdope.com/u/KCB615)\
**Post date:** [May 13, 2013, 9:16pm UTC](https://boards.straightdope.com/t/excel-intercept-slope-and-forecast-funny-math/658263/1 "2013-05-13T21:16:27Z")

</div>

I have been using an Excel spreadsheet for some chemistry-esque calculations for better than a decade, which has made its way around my industry for similar purposes. Put in a range of known values for three points on a slope (2%, 3%, and 4%) and the worksheet will spit out a percentage value for a point on the slope. Simple.

When I first wrote the sheet, I used the “Intercept” function divided by the “Slope” function to determine the unknown value. I know this can also be done with the “Forecast” function, and I am comfortable with that also. The problem I have run into is that I am getting outputs that I can’t explain (after 10 years).

Here is a data set I currently have sitting in the worksheet:

```auto

Known range values:
2% = 1.141
3% = 1.369
4% = 1.761

Unknown values (what percent does each equal)
a: 1.574
b: 1.470
c: 1.369
d: 1.416

```

My inherent mathiness says that if I put 1.369 through either intercept/slope or forecast, it should say 1.369 = 3.00%. Instead, the following results are returned:

```auto

1.574 = 3.32%
1.470 = 3.15%
1.369 = 2.82%
1.416 = 2.98%

```

The baseline range established that 1.369 = 3.00%, but the formulas (both intercept/slope and forecast) are saying it is 2.82%. Similarly, 1.416 should be above 3.00%, but the formulas say it is less.

Where in here am I losing my .18%?

---

<div class="post-metadata">

**Author:** ![ZenBeam](https://avatars.discourse-cdn.com/v4/letter/z/3ab097/32.png) [@ZenBeam](https://boards.straightdope.com/u/ZenBeam)\
**Post date:** [May 13, 2013, 9:40pm UTC](https://boards.straightdope.com/t/excel-intercept-slope-and-forecast-funny-math/658263/2 "2013-05-13T21:40:41Z")

</div>

I don’t use Excel, but you’ve got three points, and you’re fitting a line to it. It only takes two points to determine a line, so I’d guess you have a best fit line, which won’t necessarily go through any of the points you use to fit to. that line goes through 2.82% at 1.369.

---

<div class="post-metadata">

**Author:** ![ZenBeam](https://avatars.discourse-cdn.com/v4/letter/z/3ab097/32.png) [@ZenBeam](https://boards.straightdope.com/u/ZenBeam)\
**Post date:** [May 13, 2013, 9:47pm UTC](https://boards.straightdope.com/t/excel-intercept-slope-and-forecast-funny-math/658263/3 "2013-05-13T21:47:01Z")

</div>

I just did a fit to a line with x = [1.141, 1.369, 1.761] and y = [2,3,4], and the point on the line with x = 1.369 is 2.8277, so that looks like that’s what’s going on.

---

<div class="post-metadata">

**Author:** ![Strinka](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/strinka/32/512_2.png) [@Strinka](https://boards.straightdope.com/u/Strinka)\
**Post date:** [May 13, 2013, 10:37pm UTC](https://boards.straightdope.com/t/excel-intercept-slope-and-forecast-funny-math/658263/4 "2013-05-13T22:37:57Z")

</div>

If you have more than two points, then they are not guaranteed to be collinear. That is, there may not be any straight line that goes through each point.

To plot a line through several points that aren’t collinear is called [linear regression](http://en.wikipedia.org/wiki/Linear_regression). The idea is to find the line that minimizes each point’s distance to the line. None of the initial points necessarily lie on the resulting line.

---

<div class="post-metadata">

**Author:** ![KCB615](https://avatars.discourse-cdn.com/v4/letter/k/ea666f/32.png) [@KCB615](https://boards.straightdope.com/u/KCB615)\
**Post date:** [May 13, 2013, 10:49pm UTC](https://boards.straightdope.com/t/excel-intercept-slope-and-forecast-funny-math/658263/5 "2013-05-13T22:49:50Z")

</div>

That makes complete sense.

In a prior version of the spreadsheet I had used a graph to visually check that the slope was straight. I removed the graph and replaced it with a check of the 4% - 3% distance and 3% - 2% distance, giving a simple “ok” if the results were within 2.5% of each other. The formula only looked to see if it was \< 0.025. The numbers above were -0.0418, which is certainly below 0.025, but is well out of the acceptable range. That’s been fixed with an ABS() function.

Once again, you folks saved my behind - a laurel and a hearty handshake is due to you all.