Creating a Formula to Automatically Update Average Weather Temperature

[doops]

Hi,

I'm working on updating an Excel sheet that has collects weather temperatures throughout the day at different times. The data is organized by date and there are about 10-13 temperature inputs per each date. I've been trying to write a formula using "=averageif()" to create a "tool" that will automatically calculate the average when data is put in for a new date. The data is not continuous outside the specific date. I haven't been able to figure it out and feel like I'm missing something and it's much easier than what I'm trying to do.

TLDR.
I need a formula that automatically calculates the average weather temperature on the "blank" date it was collected.

 

[6StringJazzer]

the "blank" date it was collected.

I'm not following what that means.

automatically calculate the average when data is put in for a new date

This will give you the average C temperature for the most recent date, which is what it sounds like you want. Use D:D to get F temperature. If that's not quite what you want, try to reword your request.

=AVERAGEIF($B:$B,">="&INT(MAX($B:$B)),C:C)

(Just a tip: In the future if you have a data table, put the data itself into your post instead of a picture. That way we can test with your data. Most people aren't going to type all of that in.)

 

[doops]

The "blank" date is referring to the dates in the data. Such as 6/22/2021 or 6/23/2021. I'm hoping to create a formula that automatically calculates the average for each date. Here is the data too!

#	Date-Time (CST)	Ch: 1 - Temperature (°C)	Ch: 1 - Temperature (°F)
1	06/22/2021 10:00:00	24.407548828125	75.93358789​
2	06/22/2021 11:00:00	26.68126953125	80.02628516​
3	06/22/2021 12:00:00	25.866162109375	78.5590918​
4	06/22/2021 13:00:00	26.4238671875	79.56296094​
5	06/22/2021 14:00:00	25.30845703125	77.55522266​
6	06/22/2021 15:00:00	24.664951171875	76.39691211​
7	06/22/2021 16:00:00	24.922353515625	76.86023633​
8	06/22/2021 17:00:00	25.7374609375	78.32742969​
9	06/22/2021 18:00:00	26.252265625	79.25407813​

 

[6StringJazzer]

I do not see any examples of what you are calling a "blank" date. Do you mean that at the start each day's data, there is a cell with just the date, following by the date/times for that date? And each time that happens you want to show the average temp for that date? If so then:

$scratch.xlsm
	A	B	C	D	E	F
1	#	Date-Time (CST)	Ch: 1 - Temperature (°C)	Ch: 1 - Temperature (°F)	Average Temperature (°C)	Average Temperature (°F)
2		6/22/21 0:00:00			25.58492622	78.05286719
3	1	6/22/21 10:00:00	24.40754883	75.93358789
4	2	6/22/21 11:00:00	26.68126953	80.02628516
5	3	6/22/21 12:00:00	25.86616211	78.5590918
6	4	6/22/21 13:00:00	26.42386719	79.56296094
7	5	6/22/21 14:00:00	25.30845703	77.55522266
8	6	6/22/21 15:00:00	24.66495117	76.39691211
9	7	6/22/21 16:00:00	24.92235352	76.86023633
10	8	6/22/21 17:00:00	25.73746094	78.32742969
11	9	6/22/21 18:00:00	26.25226563	79.25407813
12	10	6/23/21 0:00:00			26.97381511	80.55286719
13	11	6/23/21 10:00:00	25.79643772	78.43358789
14	12	6/23/21 11:00:00	28.07015842	82.52628516
15	13	6/23/21 12:00:00	27.255051	81.0590918
16	14	6/23/21 13:00:00	27.81275608	82.06296094
17	15	6/23/21 14:00:00	26.69734592	80.05522266
18	16	6/23/21 15:00:00	26.05384006	78.89691211
19	17	6/23/21 16:00:00	26.31124241	79.36023633
20	18	6/23/21 17:00:00	27.12634983	80.82742969
21	19	6/23/21 18:00:00	27.64115452	81.75407813
TEmps

Cell Formulas
Range	Formula
E2:F21	E2	=IF($B2=INT($B2),AVERAGEIFS(C:C,$B:$B,"<="&$B2+1,$B:$B,">"&$B2),"")