Formula to identify the time & frequency of max value.

[Sindhura]

Hi,


Need some help to identify the latest time when max value is reached and how many times that max value has been reached in that particular day.

Brief example for my question:

the max value for 1st Nov is 12 and the peak usage latest time is 1600
and 12 has reached 8 times in that particular day.

Date & Time​	Max​	Total​
11/1/2023 0:00​	1​	50​
11/1/2023 1:00​	3​	50​
11/1/2023 2:00​	3​	50​
11/1/2023 3:00​	3​	50​
11/1/2023 4:00​	12​	50​
11/1/2023 5:00​	12​	50​
11/1/2023 6:00​	12​	50​
11/1/2023 7:00​	12​	50​
11/1/2023 8:00​	12​	50​
11/1/2023 9:00​	12​	50​
11/1/2023 10:00​	12​	50​
11/1/2023 11:00​	4​	50​
11/1/2023 12:00​	7​	50​
11/1/2023 13:00​	6​	50​
11/1/2023 14:00​	7​	50​
11/1/2023 15:00​	8​	50​
11/1/2023 16:00​	12​	50​
11/1/2023 17:00​	9​	50​
11/1/2023 18:00​	4​	50​
11/1/2023 19:00​	4​	50​
11/1/2023 20:00​	4​	50​
11/1/2023 21:00​	4​	50​
11/1/2023 22:00​	4​	50​
11/1/2023 23:00​	4​	50​
11/2/2023 0:00​	4​	50​
11/2/2023 1:00​	4​	50​
11/2/2023 2:00​	4​	50​
11/2/2023 3:00​	4​	50​
11/2/2023 4:00​	5​	50​
11/2/2023 5:00​	5​	50​
11/2/2023 6:00​	5​	50​
11/2/2023 7:00​	7​	50​
11/2/2023 8:00​	13​	50​
11/2/2023 9:00​	12​	50​
11/2/2023 10:00​	15​	50​
11/2/2023 11:00​	14​	50​
11/2/2023 12:00​	15​	50​
11/2/2023 13:00​	17​	50​
11/2/2023 14:00​	19​	50​
11/2/2023 15:00​	17​	50​
11/2/2023 16:00​	13​	50​
11/2/2023 17:00​	10​	50​
11/2/2023 18:00​	6​	50​
11/2/2023 19:00​	5​	50​
11/2/2023 20:00​	6​	50​
11/2/2023 21:00​	6​	50​
11/2/2023 22:00​	6​	50​
11/2/2023 23:00​	5​	50​

Output should be like:

Date​	Peak usage latest time​	Frequency (max value)​
11/1/2023​	1600​	8​

11/2/2023​

1400​

1​

Thank you in advance!

 

[hagia_sofia]

Try if this could work for you (columns A - B - C represent Date & Time - Max - Total):

List of days:

=UNIQUE(INT(A2:A49))

Peak max time for a particular day:

=LET(
day,FILTER($A$2:$B$49,INT($A$2:$A$49)=E2),
max,MAX(CHOOSECOLS(day,2)),
MOD(TAKE(SORT(FILTER(CHOOSECOLS(day,1),CHOOSECOLS(day,2)=max)),-1),1))

where E2 is the day to be calculated, e.g. 11/1/2023.

Frequency of max value for a particular day:

=LET(
day,FILTER($A$2:$B$49,INT($A$2:$A$49)=E2),
max,MAX(CHOOSECOLS(day,2)),
ROWS(FILTER(CHOOSECOLS(day,1),CHOOSECOLS(day,2)=max)))

where E2 is the day to be calculated, e.g. 11/1/2023.

 

[awoohaw]

try this:

Book1
	A	B	C	D	E	F	G	H
1	Date & Time	Max	Total
2	2023-11-01 00:00:00	1	50			Date	Peak usage latest time	Frequency (max value)
3	2023-11-01 01:00:00	3	50			11/1/2023	1600	8
4	2023-11-01 02:00:00	3	50			11/2/2023	1400	1
5	2023-11-01 03:00:00	3	50
6	2023-11-01 04:00:00	12	50
7	2023-11-01 05:00:00	12	50			2023-11-01	16:00:00	8
8	2023-11-01 06:00:00	12	50			2023-11-02	14:00:00	1
9	2023-11-01 07:00:00	12	50
10	2023-11-01 08:00:00	12	50
11	2023-11-01 09:00:00	12	50
12	2023-11-01 10:00:00	12	50
13	2023-11-01 11:00:00	4	50
14	2023-11-01 12:00:00	7	50
15	2023-11-01 13:00:00	6	50
16	2023-11-01 14:00:00	7	50
17	2023-11-01 15:00:00	8	50
18	2023-11-01 16:00:00	12	50
19	2023-11-01 17:00:00	9	50
20	2023-11-01 18:00:00	4	50
21	2023-11-01 19:00:00	4	50
22	2023-11-01 20:00:00	4	50
23	2023-11-01 21:00:00	4	50
24	2023-11-01 22:00:00	4	50
25	2023-11-01 23:00:00	4	50
26	2023-11-02 00:00:00	4	50
27	2023-11-02 01:00:00	4	50
28	2023-11-02 02:00:00	4	50
29	2023-11-02 03:00:00	4	50
30	2023-11-02 04:00:00	5	50
31	2023-11-02 05:00:00	5	50
32	2023-11-02 06:00:00	5	50
33	2023-11-02 07:00:00	7	50
34	2023-11-02 08:00:00	13	50
35	2023-11-02 09:00:00	12	50
36	2023-11-02 10:00:00	15	50
37	2023-11-02 11:00:00	14	50
38	2023-11-02 12:00:00	15	50
39	2023-11-02 13:00:00	17	50
40	2023-11-02 14:00:00	19	50
41	2023-11-02 15:00:00	17	50
42	2023-11-02 16:00:00	13	50
43	2023-11-02 17:00:00	10	50
44	2023-11-02 18:00:00	6	50
45	2023-11-02 19:00:00	5	50
46	2023-11-02 20:00:00	6	50
47	2023-11-02 21:00:00	6	50
48	2023-11-02 22:00:00	6	50
49	2023-11-02 23:00:00	5	50
50
Sheet2

Cell Formulas
Range	Formula
G7:G8	G7	=LET(ds,INT($A$2:$A$49),d,$F7,mx,$B$2:$B$49,dmx,MAX(FILTER(mx,ds=d,"")), INDEX(MOD($A$2:$A$49,1),MAX((mx=dmx)*(ROW(mx)-1)*(ds=d))))
H7:H8	H7	=LET(ds,INT($A$2:$A$49),d,$F7,mx,$B$2:$B$49,fmx,MAX(FILTER(mx,ds=d,"")), SUM((ds=d)*(fmx=mx)))

 

[Fluff]

Another option

Fluff.xlsm
	A	B	C	D	E	F	G
1	Date & Time	Max	Total
2	01/11/2023 00:00	1	50		01/11/2023	16:00:00	8
3	01/11/2023 01:00	3	50		02/11/2023	14:00:00	1
4	01/11/2023 02:00	3	50
5	01/11/2023 03:00	3	50
6	01/11/2023 04:00	12	50
7	01/11/2023 05:00	12	50
8	01/11/2023 06:00	12	50
9	01/11/2023 07:00	12	50
10	01/11/2023 08:00	12	50
11	01/11/2023 09:00	12	50
12	01/11/2023 10:00	12	50
13	01/11/2023 11:00	4	50
14	01/11/2023 12:00	7	50
15	01/11/2023 13:00	6	50
16	01/11/2023 14:00	7	50
17	01/11/2023 15:00	8	50
18	01/11/2023 16:00	12	50
19	01/11/2023 17:00	9	50
20	01/11/2023 18:00	4	50
21	01/11/2023 19:00	4	50
22	01/11/2023 20:00	4	50
23	01/11/2023 21:00	4	50
24	01/11/2023 22:00	4	50
25	01/11/2023 23:00	4	50
26	02/11/2023 00:00	4	50
27	02/11/2023 01:00	4	50
28	02/11/2023 02:00	4	50
29	02/11/2023 03:00	4	50
30	02/11/2023 04:00	5	50
31	02/11/2023 05:00	5	50
32	02/11/2023 06:00	5	50
33	02/11/2023 07:00	7	50
34	02/11/2023 08:00	13	50
35	02/11/2023 09:00	12	50
36	02/11/2023 10:00	15	50
37	02/11/2023 11:00	14	50
38	02/11/2023 12:00	15	50
39	02/11/2023 13:00	17	50
40	02/11/2023 14:00	19	50
41	02/11/2023 15:00	17	50
42	02/11/2023 16:00	13	50
43	02/11/2023 17:00	10	50
44	02/11/2023 18:00	6	50
45	02/11/2023 19:00	5	50
46	02/11/2023 20:00	6	50
47	02/11/2023 21:00	6	50
48	02/11/2023 22:00	6	50
49	02/11/2023 23:00	5	50
Data

Cell Formulas
Range	Formula
E2:E3	E2	=UNIQUE(FILTER(INT(A2:A100),A2:A100<>""))
F2:G3	F2	=LET(a,SORT(FILTER($A$2:$B$100,INT(A2:A100)=E2),{2,1},-1),HSTACK(TAKE(a,1,1),SUM(IF(INDEX(a,,2)=MAX(INDEX(a,,2)),1,0))))
Dynamic array formulas.

 

[Sindhura]

try this:

Book1
	A	B	C	D	E	F	G	H
1	Date & Time	Max	Total
2	2023-11-01 00:00:00	1	50			Date	Peak usage latest time	Frequency (max value)
3	2023-11-01 01:00:00	3	50			11/1/2023	1600	8
4	2023-11-01 02:00:00	3	50			11/2/2023	1400	1
5	2023-11-01 03:00:00	3	50
6	2023-11-01 04:00:00	12	50
7	2023-11-01 05:00:00	12	50			2023-11-01	16:00:00	8
8	2023-11-01 06:00:00	12	50			2023-11-02	14:00:00	1
9	2023-11-01 07:00:00	12	50
10	2023-11-01 08:00:00	12	50
11	2023-11-01 09:00:00	12	50
12	2023-11-01 10:00:00	12	50
13	2023-11-01 11:00:00	4	50
14	2023-11-01 12:00:00	7	50
15	2023-11-01 13:00:00	6	50
16	2023-11-01 14:00:00	7	50
17	2023-11-01 15:00:00	8	50
18	2023-11-01 16:00:00	12	50
19	2023-11-01 17:00:00	9	50
20	2023-11-01 18:00:00	4	50
21	2023-11-01 19:00:00	4	50
22	2023-11-01 20:00:00	4	50
23	2023-11-01 21:00:00	4	50
24	2023-11-01 22:00:00	4	50
25	2023-11-01 23:00:00	4	50
26	2023-11-02 00:00:00	4	50
27	2023-11-02 01:00:00	4	50
28	2023-11-02 02:00:00	4	50
29	2023-11-02 03:00:00	4	50
30	2023-11-02 04:00:00	5	50
31	2023-11-02 05:00:00	5	50
32	2023-11-02 06:00:00	5	50
33	2023-11-02 07:00:00	7	50
34	2023-11-02 08:00:00	13	50
35	2023-11-02 09:00:00	12	50
36	2023-11-02 10:00:00	15	50
37	2023-11-02 11:00:00	14	50
38	2023-11-02 12:00:00	15	50
39	2023-11-02 13:00:00	17	50
40	2023-11-02 14:00:00	19	50
41	2023-11-02 15:00:00	17	50
42	2023-11-02 16:00:00	13	50
43	2023-11-02 17:00:00	10	50
44	2023-11-02 18:00:00	6	50
45	2023-11-02 19:00:00	5	50
46	2023-11-02 20:00:00	6	50
47	2023-11-02 21:00:00	6	50
48	2023-11-02 22:00:00	6	50
49	2023-11-02 23:00:00	5	50
50
Sheet2

Cell Formulas
Range	Formula
G7:G8	G7	=LET(ds,INT($A$2:$A$49),d,$F7,mx,$B$2:$B$49,dmx,MAX(FILTER(mx,ds=d,"")), INDEX(MOD($A$2:$A$49,1),MAX((mx=dmx)*(ROW(mx)-1)*(ds=d))))
H7:H8	H7	=LET(ds,INT($A$2:$A$49),d,$F7,mx,$B$2:$B$49,fmx,MAX(FILTER(mx,ds=d,"")), SUM((ds=d)*(fmx=mx)))

Its Working!!!
Saves so much time! Thank you so much

 

[Cubist]

Adding one more to the mix.

Book1
	A	B	C	D	E	F	G
1	Date & Time	Max	Total		Date	Peak usage latest time	Frequency (max value)
2	11/1/23 0:00	1	50		11/1/23	16:00	8
3	11/1/23 1:00	3	50		11/2/23	14:00	1
4	11/1/23 2:00	3	50
5	11/1/23 3:00	3	50
6	11/1/23 4:00	12	50
7	11/1/23 5:00	12	50
8	11/1/23 6:00	12	50
9	11/1/23 7:00	12	50
10	11/1/23 8:00	12	50
11	11/1/23 9:00	12	50
12	11/1/23 10:00	12	50
13	11/1/23 11:00	4	50
14	11/1/23 12:00	7	50
15	11/1/23 13:00	6	50
16	11/1/23 14:00	7	50
17	11/1/23 15:00	8	50
18	11/1/23 16:00	12	50
19	11/1/23 17:00	9	50
20	11/1/23 18:00	4	50
21	11/1/23 19:00	4	50
22	11/1/23 20:00	4	50
23	11/1/23 21:00	4	50
24	11/1/23 22:00	4	50
25	11/1/23 23:00	4	50
26	11/2/23 0:00	4	50
27	11/2/23 1:00	4	50
28	11/2/23 2:00	4	50
29	11/2/23 3:00	4	50
30	11/2/23 4:00	5	50
31	11/2/23 5:00	5	50
32	11/2/23 6:00	5	50
33	11/2/23 7:00	7	50
34	11/2/23 8:00	13	50
35	11/2/23 9:00	12	50
36	11/2/23 10:00	15	50
37	11/2/23 11:00	14	50
38	11/2/23 12:00	15	50
39	11/2/23 13:00	17	50
40	11/2/23 14:00	19	50
41	11/2/23 15:00	17	50
42	11/2/23 16:00	13	50
43	11/2/23 17:00	10	50
44	11/2/23 18:00	6	50
45	11/2/23 19:00	5	50
46	11/2/23 20:00	6	50
47	11/2/23 21:00	6	50
48	11/2/23 22:00	6	50
49	11/2/23 23:00	5	50
Sheet1

Cell Formulas
Range	Formula
E2:E3	E2	=UNIQUE(INT(A2:A49))
F2:G3	F2	=LET(d,$A$2:$A$49,a,INT(d)=E2,b,$B$2:$B$49,k,a*(b=MAX(IF(a,b))),HSTACK(XLOOKUP(1,k,MOD(d,1),,,-1),SUM(k)))
Dynamic array formulas.