A short catalogue of the tricks I found myself relying on when trying to fit the data from my model to the curves.
1) We know that the curve we are looking for is odd, but excel doesn't. So take the choice away. Instead of W = f(Y), fit instead W/Y = g(Y^2).
2) The OFFSET worksheet function allows the creation of ranges using cells to determine the extents of the range. The general drill was to sort the data, define ranges that shared a value for X, apply an estimator to get Y coefficients, then apply another coefficient to those coefficients to measure how they depend on X.
3) Y = aX^b => ln(Y) = ln(a) + b * ln(X). To fit a power series, use a linear fit on the log, then unwind.
4) The LINEST worksheet function can also handle polynomials. LINEST(Y1:Y100,X1:X100^{1,2,3}) gives a range of 4 values - but not in the order I expected. The INDEX worksheet function extracts each coefficient from the fit. Yeah, I had expressions like INDEX(LINEST(OFFSET,OFFSET^{1,2,3})) - bleah.
5) When bouncing around a worksheet with 19,000 rows, use the name box to specify a range before invoking the graph tool. Otherwise there's a lot of plotting to be done.
6) If you suspect a power series, make sure you take the ABS of the range first, so that the errors near zero don't invalidate the estimate.
June 25, 2004 11:49 PM
| TrackBack