weighted curve fitting

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

 

[Jerry W. Lewis]

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

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

 

[Jerry W. Lewis]

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

 

[Harlan Grove]

Weighted average:
=SUMPRODUCT(xdata,weights)/SUM(weights)

...

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

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

?!

 

[Jerry W. Lewis]

Same difference with a linear function.

Jerry

...

..

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

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

 

[Harlan Grove]

Same difference with a linear function.

...

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.

 

[Jerry W. Lewis]

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

 

[Harlan Grove]

...
...

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

...

You're right. I was being too cute.