Line chart & Trend lines to derive best fit line formula

[Bounces]

Hi all,

I work in a contact center that evaluates calls on a scale from 0 - 4, 2 and above meeting expectations. We are trying to figure out a way to map out goal growth for ourselves over a period of time (how many associates to move from not meeting expectations, to meets.

I got the bright idea to ball park the numbers, graph them on a line chart in excel, and then have excel give me the formula to a trend line that I can then turn into the formula to give me the goal growth given some historic value. (EG if 36% of your team fell in the 2s, 3s, & 4s category last time how many should fall into it this time for you to be effective at growth? How many for high? How many for outstanding?).

Problem is it seems like excel is horrible at figuring out the formula to a best fit line. I am using a power 6 polynomial, because it plots to the ballpark figures pretty well, but the y= formula that excell gives me comes nowhere close to matching the best fit line.

Is this a known problem? Is there anything I can do to fix it? The excel gurus on this site have helped me before, I am sure you can help me again.

Here is a screenshot of the data table and line chart I have...

As you can see the "Best Fit Value" collumn should come close to the value in the "Increase Goal" collumn, but it is nowhere near.

Thoughts?

Thanks!

 

[JoeMo]

First, I wouldn't trust a 6th-order polynomial to predict anything. I don't think the curve on your graph reflects the 6th-order polynomial expression above it. Is the R-square value for the curve shown or for the 6th-order polynomial fit to your data?

 

[Bounces]

I hid the curve of the data because they were close enough that it made it confusing. The r-squared is on the trend line.

 

[JoeMo]

I hid the curve of the data because they were close enough that it made it confusing. The r-squared is on the trend line.

What's the equation for the trend line? Looks to me like a 2nd-order polynomial would produce a pretty good fit (R-squared > 0.95). I wouldn't trust anything beyond that.

 

[Bounces]

The curve is pretty decent, but the formula still does not give good values.

I included the formula bar in the screenshot. Please validate that I am typing the formula right. Is there anything else that I am missing to make this accurate?

 

[JoeMo]

I don't know how you arrived at that formula if your x values qre in column A and your y values in column B. Here's what I get using your data from columns A & B. You can see the best fit formula that Excel returns in cell S20.

Excel Workbook
	Q	R	S	T
19	x	y	fit	diff
20	0	15	14.455	-3.6%
21	10	12	12.113	0.9%
22	20	10	9.991	-0.1%
23	30	7	8.089	15.6%
24	40	6	6.407	6.8%
25	50	5	4.945	-1.1%
26	60	4	3.703	-7.4%
27	70	3	2.681	-10.6%
28	80	2	1.879	-6.1%
29	90	1	1.297	29.7%
30	100	0	0.935	#DIV/0!
3

 

[Bounces]

Excel arrived at the formula in the screenshot, not me. That is why I was trying to get a better fit...

I went and did the math myself (I owe an apology to my highchool math teacher, I have now used algebra 2 in the "real world") and I got y=.0015x^2-.3x+15. (That's using data points (0,15) (100,0) and (200,15) to find the formula for the binomial parabola) My formula is pretty close to the formula you have.

I am wondering where you got your formula from...did you manage to get excel to give you a best fit that works?

 

[JoeMo]

Excel arrived at the formula in the screenshot, not me. That is why I was trying to get a better fit...

I went and did the math myself (I owe an apology to my highchool math teacher, I have now used algebra 2 in the "real world") and I got y=.0015x^2-.3x+15 Which is pretty close to the formula you have.

I am wondering where you got your formula from...did you manage to get excel to give you a best fit that works?

The formula came from Excel's best fit to your data using a 2nd order polynomial. The R-squared value is 0.9891.
You have to answer the question of whether the fit is good enough for you which depends on how much error you can tolerate in your estimating.