Hello,
I'm trying to calculate average January-only returns for my data using ={AVERAGE(IF(MONTH(B3:B40)=1,$C$3:$C$40))}. In order to not manually update the range for my calculations in the future, I have included blank cells in the range of calculation.
However, adding blank cells incorrectly calculates the Average January return, because Excel assumes that blank each cell is equal to January 1, 1900 and hence assigns a value of 1 for the each blank cell.
I've tried to add a filter of YEAR > 1900, but that didn't help.
I would greatly appreciate if someone could help me to resolve this issue (please see attached Excel file).
Bookmarks