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.


[TABLE="width: 485"]
<tbody>[TR]
[TD="width: 64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ACB9CA]#ACB9CA[/URL] "]item
[/TD]
[TD="width: 40, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ACB9CA]#ACB9CA[/URL] , align: right"]1-Jan
[/TD]
[TD="width: 40, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ACB9CA]#ACB9CA[/URL] , align: right"]1-Feb
[/TD]
[TD="width: 49, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ACB9CA]#ACB9CA[/URL] , align: right"]1-Mar
[/TD]
[TD="width: 40, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ACB9CA]#ACB9CA[/URL] , align: right"]1-Apr
[/TD]
[TD="width: 47, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ACB9CA]#ACB9CA[/URL] , align: right"]1-May
[/TD]
[TD="width: 40, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ACB9CA]#ACB9CA[/URL] , align: right"]1-Jun
[/TD]
[TD="width: 40, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ACB9CA]#ACB9CA[/URL] , align: right"]1-Jul
[/TD]
[TD="width: 48, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ACB9CA]#ACB9CA[/URL] , align: right"]1-Aug
[/TD]
[TD="width: 40, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ACB9CA]#ACB9CA[/URL] , align: right"]1-Sep
[/TD]
[TD="width: 40, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ACB9CA]#ACB9CA[/URL] , align: right"]1-Oct
[/TD]
[TD="width: 49, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ACB9CA]#ACB9CA[/URL] , align: right"]1-Nov
[/TD]
[TD="width: 45, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ACB9CA]#ACB9CA[/URL] , align: right"]1-Dec
[/TD]
[TD="width: 64, bgcolor: #FFC000"]2 months
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]ABC
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]12
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]2
[/TD]
[TD="bgcolor: transparent, align: right"]3
[/TD]
[TD="bgcolor: transparent, align: right"]5
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]4
[/TD]
[TD="bgcolor: transparent, align: right"]6
[/TD]
[TD="bgcolor: transparent, align: right"]1.00
[/TD]
[/TR]
</tbody>[/TABLE]

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.