How to calculate the average of a column using other columns as a reference using excel-CodePudding

I have three columns in excel year, month value.

I want to average value considering month and year. In R language this function is done by group_by(). In excel how could this be done?

year    month   value
2019    1   12
2019    1   34
2019    2   56
2019    2   15
2020    1   16
2020    3   67
2020    4   89
2018    6   123
2018    6   45
2018    7   98
2019    3   53
2019    1   23
2020    1   12
2020    3   1

CodePudding user response：

Once the HSTACK is release we can replace the CHOOSE with it:

=LET(
    y,A2:A15,
    m,B2:B15,
    v,C2:C15,
    u,SORT(UNIQUE(HSTACK(y,m)),{1,2}),
    HSTACK(u,AVERAGEIFS(v,y,INDEX(u,0,1),m,INDEX(u,0,2))))