Hello-

I'm wondering if there is a quick way (or even a macro) that will
accomplish what I've been doing in a VERY roundabout way!

In my work, I take known periodic measurements of various values and
calculate a trend/growth rate, then project forward assuming the same
trend.

I know how to use the RATE() function to produce the growth rate from
the first and last values, however, my data is not straight-line data,
and thus I can't assume that the first or last values in the series are
"regular" and not outliers (which is assumed in the RATE() function).

What I have been doing is GRAPHING the data on a simple Excel graph,
then showing the "Trendline" and getting the equation for that. The
trendline is very useful--I presume it is a least-squares trending--and
THAT is the line I want to use for values for my RATE() function. I
then have to plot the points of that equation (y = mx + b) to get a
"smooth line" version of my original data, from which I can use the
(FV/PV)^(1/periods)-1 formula or the RATE() function.

What I am wondering is whether there is some way to simply select the
values and produce the same result without graphing--in essence, have
Excel do the "least squares" trending for me just from the numbers?

For an example, take these measures:
105
110
113
119
123
133
144
151

Graphing these, I get a trendline with the equation y = 6.619x + 94.964

Replotting this equation with the numbers 1-8 (periods), I get a new
series of numbers:

101.58
108.20
114.82
121.44
128.06
134.68
141.30
147.92

These are now a straightline progression with a constant rate of
growth, which using the RATE() function is found to be .055148...

It would be very nice if I could somehow highlight the original numbers
and get an output of .055148, which is the "best average" growth rate
for this series that does NOT assume the first nor last numbers are
typical of the growth. (Note that using the actual first and last
numbers 105 and 151 in the RATE() function gives a lowball rate of
..053273, because both of the end numbers are too high for the actual
trend). Excel obviously can calculate least-squares-type trends, since
it does it for the graph...but how does one do it with the values
alone?


Thanks!

Kent P