CODEWITHSUNDEEP
excel
T-T
T-T

Reputation: 713

Excel: Product Ifs?

I have time series returns, and I want to get the product based on date range. =product(if($B:$B,A:A,">="&$A$2,A:A,"<="&A13)) would not work because there are too many arguments for this function. Here is an example,

12/31/2014    100.30%
01/31/2015    100.72%
02/28/2015    102.00%
03/31/2015    101.04%
04/30/2015    99.79%
05/31/2015    101.04%
06/30/2015    99.46%
07/31/2015    100.71%
08/31/2015    97.05%
09/30/2015    96.21%
10/31/2015    101.88%
11/30/2015    100.21%
12/31/2015    99.68%
01/31/2016    98.06%
02/29/2016    97.74%
03/31/2016    100.83%

I want to get the product of the returns from 2015. In this case, it is 99.62%. Please help.

Upvotes: 6

Views: 13019

Answers (1)

Scott Craner
Scott Craner

Reputation: 152605

For non sorted dates, try this Array formula:

=PRODUCT(IF(($A$1:$A$16>=D3)*(A1:A16<=E3),B1:B16))

Being an Array Formula it must be confirmed with Ctrl-Shift-Enter when exiting edit mode. IF done properly Excel will put {} around the formula.

As the calculations are exponential in array formulas, avoid using full column references.

enter image description here

If the dates are sorted a non array formula can be used:

=PRODUCT(INDEX(B:B,MATCH(D3,A:A)):INDEX(B:B,MATCH(E3,A:A)))

enter image description here

Upvotes: 7

Related Questions