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