I have a "daily" reporting of a temperature - "daily", yeah, but sometimes I forget it and maybe do the reporting after 2-3 days.




I have to calculate an AVERAGE for each quarter and in fact this average temp.value is what I need to report.


I have made a sample sheet and want ONLY the GREEN columns - the red columns is only here in the sample sheet to illustrate what I mean.


Sorry for the DANISH function names just use translating list for Excel funcions !



When I ENTER the DATE and the TEMP for that day, I want the formula to calculate the AVERAGE TEMP for the QUARTER until "now" (=the date I entered !)

Attached Files

TEST.xlsx (10.9 KB, 9 views)

What's wrong with the calc you have? If the Wanted is giving you what you want, what needs to change?

Could use AVERAGEIF but would need to include year in Quarter calculation.

=YEAR(A2) & ROUNDUP(MONTH(A2)/3,0)

Then use that in AVERAGEIF.

=AVERAGEIF($B$2:$B$34,"=" & B2,$C$2:$C$34)

If you want to extract year/quarter from date value for use in formula without having to calculate in a column, sorry, don't know that. Every example I find shows using helper column.

#June7


In fact there will ONLY be entered temps for a single year, and then the formula you gave will do the job !


THX !

Takes into account year and quarter, in cell D2: copied down.

p45Cal, I tried your formula and just returns same result for all quarters which is the average of all rows.

Not at a computer at the moment, but I did this in MSOffice 365, so try entering the formula in a single cell with Ctrl+Shift+Enter (array-entering), and then copy down.
Edit: At computer now:
2024-01-02_224709.jpg

That was it. I am still using Office 2010. Works with my year/quarter calc but when I change back to the original version just calculating quarter, results for quarter 4 are wrong because the sample data crosses calendar years and includes two quarter 4 periods.

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:
In column D, since it seems the OP's Excel version will work with lambda formulae, a short formula: with hints:

2024-01-03_020723.jpg


In column E, for interest only, the derivation of that lambda formula, the long: For cross checking, a pivot table at cell G1.

Attached Files

msofficeforums51858TEST.xlsx (18.6 KB, 3 views)

Looks like you deleted Column B so C moved to become B. With that in mind, I made adjustment and the new formula works with Ctrl+Shift+Enter.

Here is a formula which works with any Excel version from ~15 latest years, and don't need Ctrl+Shift+Enter.

This formula don't work without quarter column. And I had to add an additional pair of checks to avoid Q4 from 2023 counted into Q4 from 2024.

The only way to avoid using quarter column is probably go even more back, and instead SUMIFS and COUNTIFS to use SUMPRODUCT, where instead of column values you could check expressions for those columns! But I think, keeping the quarter column (and hide it) is easier way.

Attached Files

TEST (3).xlsx (11.4 KB, 2 views)