Array formula with criteria

pholt33

Board Regular
Joined
Jan 4, 2005
Messages
202
Office Version
  1. 365
Platform
  1. Windows
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?
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Hi

I think you misplaced a parenthesis.

I guess you meant

... ,YEAR(DateExample)=D8,
 
Upvote 0
Good catch. I fixed that but then the result becomes -100.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,266
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top