Custom Trendlines - Possible?

[kage14]

I'm working with data that more or less represents cosine and sin
curves. The data was measured and I wanted a trendline that support
this, but Excel doesn't offer anything except the standard linear
logarithmic, polynomial, power, exponential, and moving average. Is i
possible to enter your own equation for a trendline? if so
what format must it follow? thanks

kag

 

[Jerry W. Lewis]

Excel will not do this for you automatically, but you can use Excel to
fit any kind of parametic model using Solver.
- Place initial guesses for the unknown parameters in worksheet cells.
- For each observed data point, add a formula in another cell that
calculates the model's predicted value based on the parameter values in
the guessed parameter cells.
- For each observation add a formula in another cell that calculated
the difference between observed and predicted ("deviations").
- Add a formula to another cell that summarizes the overall distance
between observed and predicted. The method of "least squares" (used by
SLOPE, LINEST, etc) would take this function to be =SUMSQ(deviations) or
equivalently =SUMPRODUCT(deviations), but other nonnegative overall
distance functions, such as =SUMPRODUCT(ABS(deviations)) would also be
possible.
- Use Solver to minimize the overall distance between observed and
predicted, by changing the parameter cells.

Jerry
Excel MVP

 

[Bernard V Liengme]

Hi Kage,
Let the x-values be in A1:A25, the y-values in B1:B25
We will fit y-values to y=A*COS(B*x)
In G1 enter a starting value for A, say 1
In G2 enter a starting value for B, say 1
In C1 enter =$G$1*COS($G$2*A1) to get the computed y-values; copy down to
B25
In D1 enter =(C1-B1)^2; copy down to D25
In D26 =SUM(D125)
Now use Solver to minimize D26 by varying G1 and G2 - you have done a
least-squares fit!
Alternative: omit the D column an in G3 use =SUMXMY2(B1:B25,c1:C25) - will
give the same results as in D26 - (sum of squares of deviations)

Best wishes
Bernard
www.stfx.ca/people/bliengme

 

[kage14]

Jerry and Bernard, thank you both, apparently the least squares fit i
the way to go. I will try both methods though. Thanks again, i'll le
you know how i make out.

Kag