Hourly to daily data conversion and finding min and max

[mrnassaro]

Hello Experts!

I am working on finding the daily min, max, and average from the below data. I tried to do it based on date and using Offset but every time I am getting an error. I have excel 2016, so the Filter formula doesn't work.

 

[Peter_SSs]

For the future, sample data that can be copied will generally get you faster help & more helpers. .
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

See if something like this helps.

22 10 12.xlsm
	A	B	C	D	E	F
1				Min	Max	Avg
2	1/01/2019 2:00	66	1/01/2019	15	97	54
3	1/01/2019 3:00	32	2/01/2019	12	95	41.75
4	1/01/2019 4:00	56	3/01/2019	N/A	N/A	N/A
5	1/01/2019 5:00	80
6	1/01/2019 6:00	64
7	1/01/2019 7:00	29
8	1/01/2019 8:00	43
9	1/01/2019 9:00	97
10	1/01/2019 10:00	68
11	1/01/2019 11:00	38
12	1/01/2019 12:00	27
13	1/01/2019 13:00	15
14	1/01/2019 14:00	81
15	1/01/2019 15:00	57
16	1/01/2019 16:00	42
17	1/01/2019 17:00	37
18	1/01/2019 18:00	45
19	1/01/2019 19:00	56
20	1/01/2019 20:00	60
21	1/01/2019 21:00	94
22	1/01/2019 22:00	36
23	1/01/2019 23:00	65
24	2/01/2019 0:00	95
25	2/01/2019 1:00	12
26	2/01/2019 2:00	17
27	2/01/2019 3:00	43
28
Daily

Cell Formulas
Range	Formula
D2:D4	D2	=IFERROR(AGGREGATE(15,6,B$2:B$300/(INT(A$2:A$300)=C2),1),"N/A")
E2:E4	E2	=IFERROR(AGGREGATE(14,6,B$2:B$300/(INT(A$2:A$300)=C2),1),"N/A")
F2:F4	F2	=IFERROR(AVERAGEIFS(B$2:B$300,A$2:A$300,">="&C2,A$2:A$300,"<"&C2+1),"N/A")

 

[mrnassaro]

Thank you very much for the help, it actually works!

I was wondering what did you do to select the hourly time and make them into daily ?

 

[Peter_SSs]

Thank you very much for the help, it actually works!

You're welcome. Thanks for the confirmation.

I was wondering what did you do to select the hourly time and make them into daily ?

Excel stores dates as whole numbers and times as decimals. So, for example, if you hover over cell A6 in my mini sheet you should see this

1 January 2019 is day number 43466 (counting from 1 January 1900 as day 1) and 6:00 AM is one quarter (0.25) of a day so ..
For the Max & Min results I used INT(A$2:A$300) which just chops off the hours and leaves the date.
For the Average result I looked for dates and times greater than or equal to 1/1/2019 (C2) and less than 2/1/2019 (C2 +1). That gives me all the rows some time during 1/1/2019

 

[mrnassaro]

You're welcome. Thanks for the confirmation.


Excel stores dates as whole numbers and times as decimals. So, for example, if you hover over cell A6 in my mini sheet you should see this
View attachment 76109
1 January 2019 is day number 43466 (counting from 1 January 1900 as day 1) and 6:00 AM is one quarter (0.25) of a day so ..
For the Max & Min results I used INT(A$2:A$300) which just chops off the hours and leaves the date.
For the Average result I looked for dates and times greater than or equal to 1/1/2019 (C2) and less than 2/1/2019 (C2 +1). That gives me all the rows some time during 1/1/2019

Thank you very much for taking the time to explain the process. I truly appreciate it!

 

[Peter_SSs]

No problem. Glad to help.