My formula so far is:

=AVERAGEIFS('Main Run Log'!C:C,'Main Run Log'!A:A,">=11/13/22",'Main Run Log'!A:A,"<=1/14/23",'Main Run Log'!A:A,">=1/22/23",'Main Run Log'!A:A,"<=1/28/23")

The goal is to get the data (column C) that correspond to these two date ranges and average it.

What is the correct way of doing it? Thanks.

CodePudding user response：

averageifs='Main Run Log C :C ,Main Run Log!A :A , >=11/13/22" Main Run Log A :A ,"<=1/14/23" Main Run Log A :A ,">=1/22/23" Main Run Log'

CodePudding user response：

AVERAGE With Date Conditions (DATEVALUE)

• If your date format is the default format (mm/dd/yyyy), the following should work. Also, it should work with dashes e.g. 11-13-2022 maybe even dots e.g. 11.13.2022. You can easily test it by just replacing the separators in the first date with a different date separator. It worked even with mixed separators on my system. Your feedback is expected.

  =AVERAGE(('Main Run Log'!$C2:$C21)
  *(('Main Run Log'!$A$2:$A$21>=DATEVALUE("11/13/2022"))
  *('Main Run Log'!$A$2:$A$21<=DATEVALUE("01/14/2023"))
   ('Main Run Log'!$A$2:$A$21>=DATEVALUE("01/22/2023"))
  *('Main Run Log'!$A$2:$A$21<=DATEVALUE("01/28/2023"))))
  

• The following (yyyy/mm/dd) should work on any system possibly with other date separators. If someone knows that it doesn't, any feedback is welcome.

  =AVERAGE(('Main Run Log'!$C2:$C21)
  *(('Main Run Log'!$A$2:$A$21>=DATEVALUE("2022/11/13"))
  *('Main Run Log'!$A$2:$A$21<=DATEVALUE("2023/01/14"))
   ('Main Run Log'!$A$2:$A$21>=DATEVALUE("2023/01/22"))
  *('Main Run Log'!$A$2:$A$21<=DATEVALUE("2023/01/28"))))
  

• Note that AVERAGEIFS requires ranges, while AVERAGE also works with arrays.