1

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.

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124
AaronS
  • 43
  • 6
  • 2
    share a copy / sample of your sheet with an example of the desired output – player0 Aug 19 '22 at 22:48
  • Here is a sample of the data from the sheet. I have included columns with each separate condition from the formula above. The desired output would be the average in B3. However I would expect a different result. https://docs.google.com/spreadsheets/d/1qGAwkHgkGBDEmLlqkv8vy-gy9CEqMDNHnru4_RWV3m0/edit?usp=sharing – AaronS Aug 19 '22 at 23:10
  • Access denied [You can't access](https://i.imgur.com/IYhjpfd.png), Please [share everyone](https://i.imgur.com/Oh4SUnW.gifv), Note that your email addess is visible to anyone who accessed the sheet. – Osm Aug 19 '22 at 23:26
  • 1
    Sorry access is restricted by my organization I cannot change. I am trying to find a work around. – AaronS Aug 19 '22 at 23:48
  • Brilliant Updated the Spreadsheet. Thank you. – AaronS Aug 20 '22 at 00:27

1 Answers1

2

try:

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

enter image description here

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)))))

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124
  • Its a great solution, however in my actual sheet i wont have the Columns C:G Sorry should have been more specific in the example. I could.. maybe but don't want to add the Column G that is checking all conditions and hide the column in the final sheet. – AaronS Aug 20 '22 at 00:53
  • 1
    @AaronS answer updated – player0 Aug 20 '22 at 01:02
  • 1
    Thank you, that's it. Simple really. haha syntax I swear – AaronS Aug 20 '22 at 01:03