Setting dynamic range in a formula

Hi,
I have a column of numbers and I want the following array formula to always use the last 12 entries:
=(PRODUCT(1+D1:D12/100)-1)*100
Any suggestions?
Thanks,
Phillycheese5

P.S.
Last question today :-)

What do you want the ultimate formula to do with the last 12 entries?

Add them?
Average them?

Ron

I want it to be the range for the formula I listed which is a geometric linking fomula. I can't use the GEOMEAN because it ignors negative returns.

This formula returns the range of the last 12 entries, which you can hopefully use:

=OFFSET(INDIRECT("D"&SUMPRODUCT(MAX(((D1:D50)<>"")*(ROW((D1:D50)))))),-11,,12)

Note: It does not check for blanks. If finds the last entry and returns the last 12 cells ending in the location of the last entry. If you need the last actual non-blank entries, let me know.

Does that help?

Ron

In your array formula, my formula would be this:

=(PRODUCT(1+OFFSET(INDIRECT("D"&SUMPRODUCT(MAX(((D1:D50)<>"")*(ROW((D1:D50)))))),-11,,12)/100)-1)*100

Committed with [Ctrl]+[Shift]+[Enter]

Is that what you're looking for?

Ron

Ron,
It's very close...and I'm not sure how to tweak it...
The last 12 returns I have are these:
1.40%
-5.26%
-3.87%
2.04%
3.91%
4.01%
2.77%
0.44%
-1.06%
2.31%
-3.72%
4.66%
which links for a cumulative of 7.23, and the formula you provided yields 7.63.
Not sure where the difference is...the returns only go out 2 decimals, so it's not rounding.
I really appreciate the help.
Phillycheese5