Automatic Geomean if date

I have a table with dates (column A) and returns (column B). In cell A1, I have a specific year and would like to compute the geomean of the returns in column B that matches the criteria in cell A1 (year 2016). I was able to get a static geomean forrmula (=SUMPRODUCT(GEOMEAN(B3:B7+1))-1), but I am now looking for a formula that can return the new geomean everytime a new date that matches the criteria in cell A1 is entered in column A. For example, if data in A8 is 01.06.2017, the value should remain the same. But if value is 03.03.2016, the formula should add the value in B8. Basically, I am looking for a formula similar to average if, something similar to =AVERAGE(IF(YEAR(Change!A3:A10000)=Return!$A$3,Change!B3:B10000))


A B C D
1 2016
2
3 01.02.2016 (1.3%)
4 01.03.2016 1.1%
5 01.04.2016 0.5%
6 01.05.2016 0.6%
7 01.06.2016 (0.3%)
8

Geomean do not calculate negative numbers

Hello Tim,

Thank you for your help. I am trying to work with your formula. I used the below formula to compute mean return for a stock portfolio: =SUMPRODUCT(GEOMEAN(_t[return]+1))-1, using your spreadsheet. Trying now to make the automatic recalculation work. Let me know if you can work some magic 2.

Originally Posted by Herrmenan

I have a table with dates (column A) and returns (column B). In cell A1, I have a specific year and would like to compute the geomean of the returns in column B that matches the criteria in cell A1 (year 2016).

Array-enter (press ctrl+shift+Enter instead of just Enter) the following formula:

=GEOMEAN(IF(YEAR(A3:A1000)=A1,1+B3:B1000)) - 1

Hi. I'm trying to find monthly geomean of the year 2008-2018 and dates are different. I found monthly arithmetic mean of returns by averageifs function by month, year criteria. however couldnt find geomean this way and any other way in these forum..It just calculates the mean of whole range. Is there any way to find geomean with year and month criteria?
Thanks in advance for your help, I am learning so much about Excel through these forums.

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.