Need Help with LOGEST problem

[KathyC]

Hi.

My spreadsheet has an embedded chart that shows a stock's price over time. So the X axis shows dates and the Y axis shows price. Series 1 is Price. I let the user add other series of price CAGR (compounded annual growth rate); there's High/Low/Current/Other1/Other2/AVG. And AVG is the problem.

I'm using LOGEST(known Ys, [known Xs], [const], [stats])
like this:
AVG=LOGEST($U$23:INDIRECT($AF$32),$W$23:INDIRECT($AF$35),TRUE,FALSE)-1

which gives me the AVG cagr (which I guess is really slope of line? When it comes to statistics/math, I'm horrible, sorry). Known Ys = start:stop price and known Xs = start:stop yearfrac. I originally tried using the date column for known Xs, but got a flat line.

And then to calculate each point on that line, I use:

start price * (1+CAGR) ^time passed since start date
=INDIRECT(AF$32)*(1+$F$8)^$W4

I have quadruple checked all the values in the cells and they are correct.

Attached is a screen shot of a chart. The CAGR lines are color coordinated with the data on the left side, so AVG is yellow.

So what's hapening? The AVG looks nowhere near correct does it? On some stocks it turns out LOWER than the Low CAGR. Now, I realize that the user entered hi/low may not actually be the statistically correct hi/low, but should it be that far off?

Does the fact that the time passed since the first date = 0 screw things up? If so, anyone know if it's valid statistically to skip that first month and start the AVG calculation from the next month?

I've also wondered if it's statistically valid to just add hi/low together and divide by 2. I coded it as the ALT CAGR button, and while that's much, much closer to looking right, it still doesn't look perfect.

Thanks for any help!
Kathy