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


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

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


--
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,
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


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

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(((D 1:D50)<"")*(ROW((D1:D50)))))),-11,,12))-1)

Are we there, yet?

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,
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


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