Quote:
Originally Posted by June7
However, this still requires the 'helper' calculated column so not really seeing advantage.
|
True; hadn't read the question!
In the attached, column C contains longish formula which should work in Excel 2010:
Code:
=AVERAGE(IF(((YEAR($A$2:$A$34)=YEAR(A2))*(ROUNDUP(MONTH($A$2:$A$34)/3,0)=ROUNDUP(MONTH(A2)/3,0)))<>0,$B$2:$B$34))
In column D, since it seems the OP's Excel version will work with lambda formulae, a short formula:
Code:
=QuarterAvg($A$2:$A$34,$B$2:$B$34,A2)
with hints:
2024-01-03_020723.jpg
In column E, for interest only, the derivation of that lambda formula, the long:
Code:
=LAMBDA(dates,temps,oneDate,LET(qtrs,YEAR(dates)&"|"&ROUNDUP(MONTH(dates)/3,0),oneQtr,YEAR(oneDate)&"|"&ROUNDUP(MONTH(oneDate)/3,0),AVERAGE(IF(qtrs=oneQtr,temps))))($A$2:$A$34,$B$2:$B$34,A2)
For cross checking, a pivot table at cell G1.