#1
|
|||
|
|||
I need a formula to do this !!!!
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 !) |
#2
|
||||
|
||||
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. |
#3
|
|||
|
|||
#June7
In fact there will ONLY be entered temps for a single year, and then the formula you gave will do the job ! THX ! |
#4
|
||||
|
||||
Takes into account year and quarter, in cell D2:
Code:
=AVERAGE(IF(((YEAR($A$2:$A$34)=YEAR(A2))*($B$2:$B$34=B2))<>0,$C$2:$C$34)) |
#5
|
||||
|
||||
p45Cal, I tried your formula and just returns same result for all quarters which is the average of all rows.
|
#6
|
||||
|
||||
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 |
#7
|
||||
|
||||
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. |
#8
|
||||
|
||||
Quote:
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)) Code:
=QuarterAvg($A$2:$A$34,$B$2:$B$34,A2) 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) |
#9
|
||||
|
||||
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.
|
#10
|
|||
|
|||
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. |
Tags |
excel 2013 formula |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Formula Copy Row 2 Row But Next Column In Formula From Another Tab | TimG | Excel | 3 | 04-16-2018 09:20 PM |
Formula Help | OTPM | Excel | 2 | 10-22-2017 06:38 AM |
Possible to use an existing vlookup formula to also insert correct info and trigger a SUM formula | innkeeper9 | Excel | 2 | 09-13-2016 08:59 PM |
Need help with dragging a formula and changing a reference column as I drag the formula. | LupeB | Excel | 1 | 10-22-2015 03:02 PM |
Need help with formula | zachluke | Excel | 3 | 03-13-2014 11:15 AM |