Need a better formula

[Ron99]

Hello,

I have a set of consumption data, where I am trying to count by 2 months frequency and take an average. Since I have a data from 2012 till date my formula works fine but I am tired doing the loops. Below is the sample of data from what I am trying to achieve.

item	1-Jan	1-Feb	1-Mar	1-Apr	1-May	1-Jun	1-Jul	1-Aug	1-Sep	1-Oct	1-Nov	1-Dec	2 months
ABC	0	0	12	0	2	3	5	0	0	0	4	6	1.00

<tbody>
</tbody>

Formula under the 2months which I have =AVERAGE(COUNTIF(B2:C2,">0"),COUNTIF(C2:D2,">0"),COUNTIF(D2:E2,">0"),COUNTIF(E2:F2,">0"),COUNTIF(F2:G2,">0"),COUNTIF(G2:H2,">0"),COUNTIF(H2:I2,">0"),COUNTIF(I2:J2,">0"),COUNTIF(J2:K2,">0"),COUNTIF(K2:L2,">0"),COUNTIF(L2:M2,">0"))

I am looking for a better formula may be offset function.

 

[Peter_SSs]

Try

=SUMPRODUCT((B2:L2>0)+(C2:M2>0))/COLUMNS(B2:L2)

or this array formula - entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}.

{=AVERAGE((B2:L2>0)+(C2:M2>0))}

 

[Ron99]

Thank you very much for the swift reply. It works the way I was expecting. Just a quick question, I would also like to do it for 3 months & 6 months frequency. Can you please assist.

 

[Ron99]

I have it figured out for 3 months & 6 months, but I am unable to understand how this function is working. If feasible can you please enlighten me. Thanks.

 

[Peter_SSs]

Edit: Sorry, I hadn't seen your last post. Anyway, you can compare your solution to mine.

Thank you very much for the swift reply. It works the way I was expecting. Just a quick question, I would also like to do it for 3 months & 6 months frequency. Can you please assist.

Try these

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	item	1-Jan	1-Feb	1-Mar	1-Apr	1-May	1-Jun	1-Jul	1-Aug	1-Sep	1-Oct	1-Nov	1-Dec	2 months	3 months	6 months
2	ABC	0	0	12	0	2	3	5	0	0	0	4	6	1	1.5	3.285714286
Average

 

[Ron99]

Thank you very much for your time. Have a great day!

 

[Peter_SSs]

Thank you very much for your time. Have a great day!

You're very welcome.