1

How would I work out the average of a range where it has to meet two requirements.

In the example below, I would like to calculate the average 'Score' of all of the 'Type 1' results that are within August 2022.

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124
comiconor
  • 71
  • 5

2 Answers2

1

try:

=AVERAGE(FILTER(C:C; YEAR(A:A)=2022; MONTH(A:A)=8; B:B="Type 1"))

or even AVERAGEIFS can be used

player0
  • 124,011
  • 12
  • 67
  • 124
  • not having any luck with that (even with AVERAGEIFS) – comiconor Aug 03 '22 at 17:58
  • @comiconor can you share a copy of your sheet? – player0 Aug 03 '22 at 17:59
  • 1
    Nevermind. - I just noticed 'YEAR' was spelt wrong in your formula. - Now working perfectly. Thank you! – comiconor Aug 03 '22 at 18:00
  • @comiconor yup, sry, fixed – player0 Aug 03 '22 at 18:02
  • =8 not =B month – Osm Aug 03 '22 at 18:07
  • @Osm thats an 8 not B – player0 Aug 03 '22 at 18:15
  • ah i was looking to the edit before and he has the date format wrong there is no month 8 in date column. X_X – Osm Aug 03 '22 at 18:19
  • How would the formula be if you added another condition. E.g column D now has a 'Name category. So, I would like to calculate the average 'Score' of all of the 'Type 1' results that are within August 2022 but are submitted by 'Dave'. – comiconor Aug 03 '22 at 18:57
  • @comiconor `=AVERAGE(FILTER(C:C; YEAR(A:A)=2022; MONTH(A:A)=8; B:B="Type 1"; D:D="Dave"))` – player0 Aug 03 '22 at 19:05
  • @player0 Last question if you don't mind. - In that formula, can it return the result of '0' if there are no matches to the condition. - At the moment I just get a #div/0. This formula is contained within an IF statement for my sheet. =IF(A2="August 2022", AVERAGE(FILTER('Results Query'!AH:AH, YEAR('Results Query'!A:A)=2022, MONTH('Results Query'!A:A)=8, 'Results Query'!D:D="Queries"))) – comiconor Aug 03 '22 at 23:16
  • @comiconor sure, try: `=IFERROR(AVERAGE(FILTER('Results Query'!AH:AH, YEAR('Results Query'!A:A)=2022, MONTH('Results Query'!A:A)=8, 'Results Query'!D:D="Queries")), 0)` – player0 Aug 03 '22 at 23:52
0
  • Fix th date format first there is no August in your dates

enter image description here

To look like this

enter image description here

Try this, or see Example

=IFERROR(AVERAGE(FILTER(E2:E, YEAR(B2:B)=2022, MONTH(B2:B)=8, C2:C="Type 1",D2:D=F5 )),0)

enter image description here

To get Unique Names in cell I2

=UNIQUE(D2:D)
Osm
  • 2,699
  • 2
  • 4
  • 26
  • the date is not necessarily wrong. depends on the locale settings but for example, in my country, we start dates with the day so 01/08/2022 = 1st Aug 2022 – player0 Aug 03 '22 at 18:33
  • How would the formula be if you added another condition. E.g column D now has a 'Name category. So, I would like to calculate the average 'Score' of all of the 'Type 1' results that are within August 2022 but are submitted by 'Dave'. – comiconor Aug 03 '22 at 18:58
  • @comiconor the answer is updated with the example sheet – Osm Aug 03 '22 at 19:13
  • Last question if you don't mind. - In that formula, can it return the result of '0' if there are no matches to the condition. - At the moment I just get a #div/0 – comiconor Aug 03 '22 at 19:22
  • In my example returns #div/0 ?! – Osm Aug 03 '22 at 19:28
  • Its return No Match see [this](https://i.imgur.com/1eKefdP.png) – Osm Aug 03 '22 at 19:30
  • `0` indicate that the record is present but the avrage is `0` if you deal with negative and positive values, just keep in mind, ill [change](https://i.imgur.com/MqtNNBy.png) it anyways. – Osm Aug 03 '22 at 19:36
  • I can't seem to get mine working. - The working one I'm using has an IF statement in front of this formula. It works fine, but cannot get it to return 0 when appropriate. My forumla : -- - - =IF(A2="August 2022", AVERAGE(FILTER('Results Query'!AH:AH, YEAR('Results Query'!A:A)=2022, MONTH('Results Query'!A:A)=8, 'Results Query'!D:D="Queries"))) – comiconor Aug 03 '22 at 19:46
  • I added the example sheet to the anser and [here](https://docs.google.com/spreadsheets/d/1KiVwULl72RGV8uiyCyW0WLRN_vZv96QjeIW5K4LyoI0/edit?usp=sharing) use it directly, im checking your formula now. – Osm Aug 03 '22 at 19:50