Average IF uneven data

[drakaz]

Hi,

I am trying to take averages of monthly returns to make them annual (just by multiplying by 12). However, problem is that data is not constant and it brakes in certain months, therefore I cannot just do the 12 month average as now did in row H. What would be the the formula for such a problem.
I could probably do it by doing 12x2 IF statements but that doesn't seem reasonable as doing this way my excel would crash because of multiple loops through 100k+ data calculations.

Thank you for your answer.

Regards,
drakaz

[FDibbins]

Heres how I would do this.

Create a small table, years dow, months across...

	I	J	K	L
1		1	2	3
2	2002	0.022891	-0.02174	0.091241
3	2003	-0.01931	-0.01604	0.024798
4	2004	0.03545	0.041509	0.00889
5	2005	-0.01978	0.004105	0.015111
6	2006	0.039835	0.017458	0.023493
7	2007	0.035673	-0.02929	0.024518
8	2008	-0.06859	-0.01822	-0.00537
9	2009	-0.10177	-0.10594	0.158638
10	2010	0.003369	0.037573	0.140448
11	2011	-0.01194	0.0347	-0.00596
12	2012

Then in J2, copied down and across...
=IFERROR(AVERAGEIFS($G:$G,$D:$D,$I2,$C:$C,J$1),"")

I am trying to take averages of monthly returns to make them annual (just by multiplying by 12).

That is mathematicaly incorrect. You cannot calc a yearly avg by * monthly avg, you need to take the base data and calc it that way

[drakaz]

That could work if I could create a small table, but there's more than 70000 columns with different months, years but a third axis - company ticker, which wouldn't allow to create 2D table I suppose, or I am missing something?

Statistically multiplying monthly average by 12 to get the annual one is almost correct with only a small error, one could also multiply the last, 12th, month and get the same thing, only maybe with higher error, but still in line with returns.