Excel - avg, min, max and 13:00 per each day

[beruf3]

On the ''Sheet1'' I have hourly values of temperatures, that looks like this:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Time[/TD]
[TD]Temp[/TD]
[/TR]
[TR]
[TD]1.1.2017 0:00:00[/TD]
[TD]-5[/TD]
[/TR]
[TR]
[TD]1.1.2017 1:00:00[/TD]
[TD]-5[/TD]
[/TR]
[TR]
[TD]1.1.2017 2:00:00[/TD]
[TD]-6[/TD]
[/TR]
[TR]
[TD]1.1.2017 3:00:00[/TD]
[TD]-6[/TD]
[/TR]
[TR]
[TD]1.1.2017 4:00:00[/TD]
[TD]-7[/TD]
[/TR]
[TR]
[TD]1.1.2017 5:00:00[/TD]
[TD]-7[/TD]
[/TR]
[TR]
[TD]1.1.2017 6:00:00[/TD]
[TD]-7[/TD]
[/TR]
</tbody>[/TABLE]

and that goes till the end of the year.

On the ''Sheet2'' I need to calculate avg, min, max, and at 13:00 hours for each day, so that needs to look like this:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Day[/TD]
[TD]Avg[/TD]
[TD]Min[/TD]
[TD]Max[/TD]
[TD]13:00[/TD]
[/TR]
[TR]
[TD]01.01.2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]02.01.2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]03.01.2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]04.01.2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

So I've managed to get it for AVG:

Avg = AVERAGEIFS(Sheet1!B;Sheet1!A;">="&DATE(YEAR(Sheet2!A2);MONTH(Sheet2!A2);DAY(Sheet2!A2));Sheet1!A;"<"&DATE(YEAR(Sheet2!A3);MONTH(Sheet2!A3);DAY(Sheet2!A3)))

But I need help with Min, Max and especially at 13:00 hours.

 

[Fennek]

Hi,

have you tried a pivot-table for avg, min and max?

Having this the temperature at 13:00 would be match of the day in pivot & 13/24.

regards

 

[beruf3]

Yes, I have.
But I have two days in a year with daylight saving time,
so in those days you have 23 and 25 hours, not 24,
and I'd like to use this file over many years,
so I'd like it to be as a formula.