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


--
Phillycheese5
------------------------------------------------------------------------
Phillycheese5's Profile: http://www.excelforum.com/member.php...o&userid=24196
View this thread: http://www.excelforum.com/showthread...hreadid=378121

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

Add them?
Average them?

Ron


--
Ron Coderre
------------------------------------------------------------------------
Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419
View this thread: http://www.excelforum.com/showthread...hreadid=378121

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.


--
Phillycheese5
------------------------------------------------------------------------
Phillycheese5's Profile: http://www.excelforum.com/member.php...o&userid=24196
View this thread: http://www.excelforum.com/showthread...hreadid=378121

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
------------------------------------------------------------------------
Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419
View this thread: http://www.excelforum.com/showthread...hreadid=378121

In your array formula, my formula would be this:

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

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

Is that what you're looking for?

Ron


--
Ron Coderre
------------------------------------------------------------------------
Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419
View this thread: http://www.excelforum.com/showthread...hreadid=378121

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


--
Phillycheese5
------------------------------------------------------------------------
Phillycheese5's Profile: http://www.excelforum.com/member.php...o&userid=24196
View this thread: http://www.excelforum.com/showthread...hreadid=378121