I am working with a dataset that has multiple entries per month with different values. For instance, a row of data will have a certain month + year, as well as a value. I’d like to find the average value of each month, and have it automatically display the averages of the past five months in a way where I don’t have to manually update VLOOKUP for a month etc. Basically, as I add newer months in I want the formula to automatically give average value of each month for the last five months.
Example sheet:
https://docs.google.com/spreadsheets/d/1cx1_V3TVsGinKrFjHYeZYFWptn5KjemOxMMQMFP5v7w/edit?gid=0#gid=0
Trying to use TOROW, CHOSECOLS, and QUERY, but something isn’t working.
Assuming that in your actual data, the months are listed as dates that a formula can reference, and more specifically, the first of the month, here’s a formula you could use in cell E3 that would fill out the entire row of months:
=LET(months,{-2,-3,-4,-5,-6},MAP(months,LAMBDA(k,AVERAGE(index(filter(A:B,A:A=EOMONTH(today(),k)+1),,2)))))
0