Setting dynamic range in a formula

P

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

Ron Coderre

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

Add them?
Average them?

Ron
 
P

Phillycheese5

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

Ron Coderre

This formula returns the range of the last 12 entries, which you ca
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 an
returns the last 12 cells ending in the location of the last entry. I
you need the last actual non-blank entries, let me know.

Does that help?

Ro
 
R

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?

Ro
 
P

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
 
R

Ron Coderre

When I run your numbers in a separate range and use your origina
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?

Ro
 
P

Phillycheese5

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

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?

Ro
 
P

Phillycheese5

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