Hi!
I want to perform the Array formula below.

Product(1+(number1,number2,...))-1 to calculate a rate of return
for the percentages in column B below.

A B
11/30/2009 1.35%
12/31/2009 -2.96%
1/31/2010 1.27%

But since the date range may change on the fly (and may even be quite large)
I thought to use "vlookup" on one initial date to find the first date in the
range then "offset" that result to pull the subsequent percents into the
product formula.

I can't get the formula to work. Or if you know a better way please let me
know. There can be up to 120 percentage numbers so maybe there is a better
way?

PS The result should be -0.4062% or -.004062

Thank you,
Orlando

To clarify further, in the Product formula - it is the portion (number1,
number2, ....) that I am having a problem with. I was trying to use vlookups
for this with nested offset but that doesn't work.

Thanks.


"ORLANDO VAZQUEZ" wrote:

Hi!
I want to perform the Array formula below.

Product(1+(number1,number2,...))-1 to calculate a rate of return
for the percentages in column B below.

A B
11/30/2009 1.35%
12/31/2009 -2.96%
1/31/2010 1.27%

But since the date range may change on the fly (and may even be quite large)
I thought to use "vlookup" on one initial date to find the first date in the
range then "offset" that result to pull the subsequent percents into the
product formula.

I can't get the formula to work. Or if you know a better way please let me
know. There can be up to 120 percentage numbers so maybe there is a better
way?

PS The result should be -0.4062% or -.004062

Thank you,
Orlando