Array formula with criteria

[pholt33]

Hello,
I have an array formula that I want to update to make it more useful.

{=(PRODUCT(1+$a$2:$a$28/100)-1)*100}

That calculates the investment return for an entire time period, lets say 2006-07. I want to change the formula so that it will calculate the return only for the values within a specified annual period, say 2006 based on the date values and a lookup cell.

I tried doing this but it gave me the full period return.
{=(PRODUCT(IF(YEAR(DateExample=D8), (1+ReturnExample/100),0))-1)*100}

DateExample is A2:A28
ReturnExample is B2:B28

Here is a simple example...
https://cdn1.imggmi.com/uploads/2019/9/25/fd891ddcf4fa966d18bd60ea765287c5-full.jpg

How can I get E8 to update if I change the value in D8 to 2007?

 

[pgc01]

Hi

I think you misplaced a parenthesis.

I guess you meant

... ,YEAR(DateExample)=D8,

 

[pholt33]

Good catch. I fixed that but then the result becomes -100.

 

[pgc01]

That would mean that the result of the product is zero, which is the case because you set the default value to zero. If you multiply by zero you get zero.

Try 1 or nothing,

=(PRODUCT(IF(YEAR(DateExample=D8), (1+ReturnExample/100),1))-1)*100

 

[pholt33]

Changing that 0 to 1 worked. Thanks!!

 

[pgc01]

You're welcome. Thanks for the feedback.