0

I need to average the values that share the same ID and have been published in the same or previous months. I have to use ARRAYFORMULA due to the volume of rows.

This is an iteration of a question I asked last week: How to use AVERAGEIFS within ARRAYFORMULA, which I've tried to adapt for this, unsuccessfully.

As it can be a little confusing, I have created this file with dummy data. Although year and month were included in the ID at the beginning, I have split them into columns to make filtering easier.

Note that February average includes January values with the same ID, March includes January, February and March, etc. When the year changes, the average restarts. I suppose it could be done by comparing the values of the year (equals) and month (equals or lower), but I don't know how to insert the comparator into the last-day formula.

Thank you all for your time and effort.

player0
  • 124,011
  • 12
  • 67
  • 124
lozgarrido
  • 33
  • 1
  • 7

1 Answers1

1

try:

=ARRAYFORMULA(IFNA(VLOOKUP(A2:A&B2:B&C2:C; QUERY({A2:A&B2:B&C2:C\D2:D}; 
 "select Col1,avg(Col2) 
  where Col2 is not null 
  group by Col1 
  label avg(Col2)''"; 0); 2; 0)))

0

or if months does not matter use:

=ARRAYFORMULA(IFNA(VLOOKUP(A2:A&B2:B; QUERY({A2:A&B2:B\D2:D}; 
 "select Col1,avg(Col2) 
  where Col2 is not null 
  group by Col1 
  label avg(Col2)''"; 0); 2; 0)))

0

player0
  • 124,011
  • 12
  • 67
  • 124
  • Thanks a lot, but I think this doesn't work. If you check rows 7-15, the results don't match with the expected (only the first month of each ID is right). – lozgarrido Oct 28 '19 at 16:48