Setting dynamic range in a formula

[Phillycheese5]

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

[Ron Coderre]

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

Add them?
Average them?

Ron

[Phillycheese5]

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.

[Ron Coderre]

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

[Ron Coderre]

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

[Phillycheese5]

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

[Ron Coderre]

When I run your numbers in a separate range and use your original formula on it, I get:

G1: =(PRODUCT(1+F1:F12/100)-1)*100
G1: =0.0762640594740516

F1: 1.40%
F2: -5.26%
F3: -3.87%
F4: 2.04%
F5: 3.91%
F6: 4.01%
F7: 2.77%
F8: 0.44%
F9: -1.06%
F10: 2.31%
F11: -3.72%
F12: 4.66%

How do you get the 7.23?

Ron

[Phillycheese5]

Ron,
I don't know why we're not getting the same numbers???? I went back and re-did the formula, and used the =(1+A1)*(1+A2)...*(1+A12)-1 and still got 7.23.
This is very curious...not sure why this is occurring? Any thoughts??
The exact percent I get is 7.229191661
Phillycheese5

[Ron Coderre]

Got it!!

Your formula doesn't need to divide/multiply by 100 for those values.

Here's the new formula:

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

Are we there, yet?

Ron

[Phillycheese5]

Ron,
That did the trick! Although, I still don't understand how the formula would give different results if you use 0.075 and use the /100 and *100 versus having the data as 7.50% to start out...but this definitely gets me through the day.
Thanks a lot!
Phillycheese5