Extract Max Value (Minutes) per day

[Cyril Beki]

I have Machine Problem Data with multiple records per day. I would like to extract the highest minutes per day. How do I get excel to do this ?
I have 17 Columns but Machine Problem Data is in column 13 while the Duration (minutes) take to fix the machine is in column 19

For example: raw data looks as follows

No	Date	Something	Something	Something	Problem	Downtime	Uptime	Duration(Minutes)	Something
1	10/1/2021	N/A	N/A	N/A	Belting	21:30	22:00	30:00
2	10/1/2021	N/A	N/A	N/A	Cylinder	23:00	01:00	120:00
3	10/2/2021	N/A	N/A	N/A	Cylinder	04:00	04:30	30:00
4	10/2/2021	N/A	N/A	N/A	Piping	06:00	08:00	120:00

Max per day table would look as follows:

Date	Problem	Max Duration
10/1/2021	Cylinder	120:00
10/2/2021	Piping	120:00

I already try to use pivot to extract maximum value but i get this error (refer image). I need max per day data to create line graph. I hope someone can help me with this. Thanks in advance

 

[jasonb75]

Are you limited to excel 2016? It would be much easier with dynamic arrays in office 365 / excel 2021

Book1
	A	B	C	D	E	F	G	H	I	J
1	No	Date	Something	Something	Something	Problem	Downtime	Uptime	Duration(Minutes)	Something
2	1	10/01/2021	N/A	N/A	N/A	Belting	21:30:00	22:00:00	30:00
3	2	10/01/2021	N/A	N/A	N/A	Cylinder	23:00:00	01:00:00	120:00
4	3	10/02/2021	N/A	N/A	N/A	Cylinder	04:00:00	04:30:00	30:00
5	4	10/02/2021	N/A	N/A	N/A	Piping	06:00:00	08:00:00	120:00
6
7		10/01/2021	Belting	30:00
8		10/01/2021	Cylinder	120:00
9		10/02/2021	Piping	120:00
Sheet27

Cell Formulas
Range	Formula
I2:I5	I2	=MOD(H2-G2,1)
B7:D9	B7	=FILTER(FILTER(B2:I5,I2:I5=MAXIFS(I2:I5,F2:F5,F2:F5),""),{1,0,0,0,1,0,0,1})
Dynamic array formulas.

 

[Cyril Beki]

How did you try setting up the pivot? I would have used Date and Problem as row headers (either one first depending on preference), then dragging Duration into the values box.

Book1
	A	B	C	D	E	F	G	H	I	J
1	No	Date	Something	Something	Something	Problem	Downtime	Uptime	Duration(Minutes)	Something
2	1	10/01/2021	N/A	N/A	N/A	Belting	21:30:00	22:00:00	30:00
3	2	10/01/2021	N/A	N/A	N/A	Cylinder	23:00:00	01:00:00	120:00
4	3	10/02/2021	N/A	N/A	N/A	Cylinder	04:00:00	04:30:00	30:00
5	4	10/02/2021	N/A	N/A	N/A	Piping	06:00:00	08:00:00	120:00
6
7
8		Row Labels	Max of Duration(Minutes)
9		10/01/2021	120:00
10		Belting	30:00
11		Cylinder	120:00
12		10/02/2021	120:00
13		Cylinder	30:00
14		Piping	120:00
15		Grand Total	120:00
Sheet27

Cell Formulas
Range	Formula
I2:I5	I2	=MOD(H2-G2,1)

Hello @Jason

The pivot i used is something like this, everything when fine when i picked Max of duration but when i change it to Max of Duration (Minute). The error pop up (previous error i show in previous post)

 

[jasonb75]

I think that the times in that column could be text, which will be a problem. When I copied your table to excel I had to fix some of the data but I though that it was just because of the way that you copied the table to the forum. Check the formatting and the actual value of the duration times in the source table. If you change the format to general and the times change to decimal numbers then they are good. If they remain as times then they are bad.

I'm done here for the day, will check back in the morning to see if you've been able to make any progress.

 

[Cyril Beki]

I think that the times in that column could be text, which will be a problem. When I copied your table to excel I had to fix some of the data but I though that it was just because of the way that you copied the table to the forum. Check the formatting and the actual value of the duration times in the source table. If you change the format to general and the times change to decimal numbers then they are good. If they remain as times then they are bad.

I'm done here for the day, will check back in the morning to see if you've been able to make any progress.

I already fix my issue but now i only wanted the line chart to show only max value based on day. How can i do that, refer image for reference

 

[jasonb75]

As the original question was about extracting the max and not creating a chart from the extracted data it would probably be beneficial if you started a new thread for this part.

 

[Cyril Beki]

As the original question was about extracting the max and not creating a chart from the extracted data it would probably be beneficial if you started a new thread for this part.

okay, thanks for the help @Jason @hrayani, your both solution works perfectly

 

[Cyril Beki]

Are you limited to excel 2016? It would be much easier with dynamic arrays in office 365 / excel 2021

Book1
	A	B	C	D	E	F	G	H	I	J
1	No	Date	Something	Something	Something	Problem	Downtime	Uptime	Duration(Minutes)	Something
2	1	10/01/2021	N/A	N/A	N/A	Belting	21:30:00	22:00:00	30:00
3	2	10/01/2021	N/A	N/A	N/A	Cylinder	23:00:00	01:00:00	120:00
4	3	10/02/2021	N/A	N/A	N/A	Cylinder	04:00:00	04:30:00	30:00
5	4	10/02/2021	N/A	N/A	N/A	Piping	06:00:00	08:00:00	120:00
6
7		10/01/2021	Belting	30:00
8		10/01/2021	Cylinder	120:00
9		10/02/2021	Piping	120:00
Sheet27

Cell Formulas
Range	Formula
I2:I5	I2	=MOD(H2-G2,1)
B7:D9	B7	=FILTER(FILTER(B2:I5,I2:I5=MAXIFS(I2:I5,F2:F5,F2:F5),""),{1,0,0,0,1,0,0,1})
Dynamic array formulas.

Is there other formula for dynamic array ? limited to excel 2016

 

[jasonb75]

Dynamic arrays are exclusive to the latest version of excel. I don't think that an alternative with regular formulas will work but I'll have a look at it when I get chance.