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

beruf3

New Member
Joined
Nov 20, 2015
Messages
43
On the ''Sheet1'' I have hourly values of temperatures, that looks like this:

TimeTemp
1.1.2017 0:00:00-5
1.1.2017 1:00:00-5
1.1.2017 2:00:00-6
1.1.2017 3:00:00-6
1.1.2017 4:00:00-7
1.1.2017 5:00:00-7
1.1.2017 6:00:00-7

<tbody>
</tbody>

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:

DayAvgMinMax13:00
01.01.2017
02.01.2017
03.01.2017
04.01.2017

<tbody>
</tbody>

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.
 
Last edited:

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,304
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top