The Line Chart on the left demonstrates the incorrect coefficients. To simplify things, I put the coefficients in E2:F2 instead of X1:Y1. I hope the images below clarify things for you. Use a Scatter Chart, instead, optionally choosing the Straight Line subtype if you want it to look like a Line Chart. The trendline is wrong because the Line Chart trendline uses x-values 1, 2, 3., regardless of what you display in the x-axis. Yes, it is always best to share some data.Īnd I see that I'm not the only one who can't follow instructions: two people (shg and me) have told you not to use Line Chart.
In that case, resort to using the coefficients in the trendline label. Then enter the following formula into C2 and copy down:Ĭaveat: LINEST might not work for some (extreme) data, but the chart trendline might work. Select X1:Y1 and array-enter (press ctrl+shift+Enter instead of just Enter) the following formula: Then enter the following formula into C2 and copy down: Then I would copy the coefficients into cells for example, -0.082 into X.2 into Y1. I like the Scientific format with 14 decimal places because that displays all values with the most precision that Excel formats, regardless of their magnitude.
However, that is not likely to reproduce good results because the coefficients 1939.2 and 0.082 do not have sufficient precision.įormat the trendline label so that the coefficients have more precision. Thus, column C contains the trendline estimated y-values. Ostensibly, you might enter formulas of the form =1939.2*EXP(-0.082*A2) starting in C2. But the first method might be easier to understand. The second method, using LINEST, is usually better. There are two ways to use the trendline formula. for the x-axis, not the actual x-axis values. The trendline of a Line chart can be misleading because it always uses 1, 2, 3. First, be sure that you are using an XY Scatter chart (perhaps with line subtype), not a Line chart.
0 kommentar(er)
