weighted curve fitting

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there a way to weight a curve fit within excel 2000? I'm trying to fit data several sets of data (x, y) to either 1st order linear regressions or 2nd order polynomials. I'd like to be able to weight the smaller values (closest to my y-intercept) more than the larger values.
Any help would be greatly appreciated.
 
Weighted average:
=SUMPRODUCT(xdata,weights)/SUM(weights)

Weighted slope:
=SUMPRODUCT(ydata-wt_ave_y,xdata-wt_ave_x,weights)/SUMPRODUCT((xdata-wt_ave_x)^2,weights)
Weighted Intercept:
=wt_ave_y - wt_slope*wt_ave_x

For higher polynomials, Use solver to minimize the weighted sum of squares

Jerry
Excel MVP
Is there a way to weight a curve fit within excel 2000? I'm trying
Click to expand...
to fit data several sets of data (x, y) to either 1st order linear
Click to expand...
regressions or 2nd order polynomials. I'd like to be able to weight
Click to expand...
 
Or for polynomials, you could use
=MMULT(
MINVERSE(MMULT(MMULT(TRANSPOSE(Xmat),Wmat),Xmat)),
MMULT(MMULT(TRANSPOSE(Xmat),Wmat),Yvec) )

where for a quadratic, Xmat is
1 x1 x1^2
1 x2 x2^2
1 x3 x3^2
...

and Wmat is the diagonal matrix of the weights.

Jerry
 
Weighted average:
=SUMPRODUCT(xdata,weights)/SUM(weights)
Click to expand...
...

Shouldn't you be using weights/SUM(weights) as in

=SUMPRODUCT(xdata,weights/SUM(weights))

?!
 
Same difference with a linear function.

Jerry

Harlan said:
...

..

Shouldn't you be using weights/SUM(weights) as in

=SUMPRODUCT(xdata,weights/SUM(weights))
Click to expand...
 
Same difference with a linear function.
Click to expand...
...

I knew I should have been explicit. Wouldn't using weights/SUM(weights) inside
the SUMPRODUCT reduce the off chance of adverse truncation error. You're on
record that even averages can suffer from truncation error, though I'm too lazy
to search Google to provide a link.
 
You are correct that overflows and roundoff errors can occur with first
moment calculations (though not as easily as with higher order moments).
You are also correct about my contention that any general purpose
averaging function should (unlike GEOMEAN) be able to average any and
all inputs that do not individually overflow or underflow. Hence my
contention that AVERAGE, STDEV, etc. should use updating algorithms (the
keywords to search on).

Unlike an updating algorithm (which is not easily specified using only
worksheet formulas), neither SUMPRODUCT(xdata,weights)/SUM(weights) nor
SUMPRODUCT(xdata,weights/SUM(weights)) is guaranteed not to overflow
simply because none of the inputs do. Hence my preference for fewer
divides in this case (though if I were writing it as a VBA function I
would use an updating algorithm).

Jerry
 
...
...
Unlike an updating algorithm (which is not easily specified using only
worksheet formulas), neither SUMPRODUCT(xdata,weights)/SUM(weights) nor
SUMPRODUCT(xdata,weights/SUM(weights)) is guaranteed not to overflow
simply because none of the inputs do. Hence my preference for fewer
divides in this case (though if I were writing it as a VBA function I
would use an updating algorithm).
Click to expand...
...

You're right. I was being too cute.
 
Back
Top