Setting dynamic range in a formula

  • Thread starter Thread starter Phillycheese5
  • Start date Start date
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 :-)
 
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 geometri
linking fomula. I can't use the GEOMEAN because it ignors negativ
returns
 
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
 
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
 
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
 
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
 
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
 
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
 
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
 
Back
Top