Google Sheets, How to use AVERAGEIF with Duration criteria-CodePudding

Here is the formula I am trying to use. Google sheets keeps giving me the Div/zero error.

=AVERAGEIF(G4:G1000,OR(AND(NOT(ISNA(MATCH(HOUR(G4:G1000),{6,14,22}, 0))), ISBETWEEN(MINUTE(G4:G1000), 25, 35))
,AND(NOT(ISNA(MATCH(HOUR(G4:G1000),{0,8,16}, 0))), ISBETWEEN(MINUTE(G4:G1000), 28, 42))
,AND(NOT(ISNA(MATCH(HOUR(G4:G1000),{5,13,21}, 0))), ISBETWEEN(MINUTE(G4:G1000), 0, 12))
,AND(NOT(ISNA(MATCH(HOUR(G4:G1000),{2,10,18}, 0))), ISBETWEEN(MINUTE(G4:G1000), 25, 45))),H4:H1000)

*edit Image of the Samplesheet I have broken out the expression into individual parts to try and find the problem with no luck.

CodePudding user response：

try:

=AVERAGE(FILTER(B4:B, REGEXMATCH(FLATTEN(QUERY(
 TRANSPOSE((C4:G=FALSE)*1),,9^9)), "0")=FALSE))

update:

=AVERAGE(FILTER(B4:B, 0=
 ((NOT(ISNA(MATCH(HOUR(A4:A),{6,14,22}, 0))))*(ISBETWEEN(MINUTE(A4:A), 25, 35))) 
 ((NOT(ISNA(MATCH(HOUR(A4:A),{0, 8,16}, 0))))*(ISBETWEEN(MINUTE(A4:A), 28, 42))) 
 ((NOT(ISNA(MATCH(HOUR(A4:A),{5,13,21}, 0))))*(ISBETWEEN(MINUTE(A4:A), 0,  12))) 
 ((NOT(ISNA(MATCH(HOUR(A4:A),{2,10,18}, 0))))*(ISBETWEEN(MINUTE(A4:A), 25, 45)))))