PDA

View Full Version : Solved: function to calculate average



k_v_deepu
08-27-2008, 06:56 AM
Hi Everybody

I have calculate average of 6 months, my columns are as below

Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
150 130 100 120 125 130 140 135

in my function i should select all the 12 columns but it should calculate
the average of the last 6 months only. In the above example it should
give the average of Mar - Aug

can anyone help how to go about for this

Thanks
Deepak

mikerickson
08-27-2008, 07:01 AM
Perhaps something like
=AVERAGE(OFFSET(G2,0,-COUNTBLANK(A2:L2),1,6))

k_v_deepu
08-27-2008, 07:07 AM
but my columns are not in order, there is gap of 11 columns between each
month

Bob Phillips
08-27-2008, 08:13 AM
=SUM(N(OFFSET(A2,0,LARGE(IF((MOD(COLUMN(A2:EC2),12)-COLUMN(A2)=0)*(A2:EC2<>""),COLUMN(A2:EC2)),{1,2,3,4,5,6})-1)))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually.
When editing the formula, it must again be array-entered.

k_v_deepu
08-27-2008, 09:46 PM
wow its really great....