Excel Seconds Conversion, find min, max and average values

[mzafar1076]

Hello Everyone, I would appreciate if I can get some help.

Please see the attached data sheet.

From this sheet, all the data is in seconds and miliseconds.

1. Is there a way to find min, max average values for a given date? For example, for 08-22 find min,max and average transaction times.

2. Is there a formula that would help us find how many transactions ran longer than 5 seconds on a given day?
3. Where we have more than 60 seconds convert it to minutes and seconds?

4. Is there an easier way to average three transactions per hour?

See the attached sheet.

Transaction Name	Time	8/22/2017	8/24/2017	8/29/2017	8/31/2017	9/5/2017	9/7/2017
Transac-1	7:03	1.32	1.29	1.54	4.45	4.51	1.53
Transac-1	7:06	3.4	1.33	1.36	3.32	3.43	1.49
Transac-1	7:09	1.3	3.39	1.47	1.3	3.33	1.33
Transac-1	7:12	1.29	1.55	1.48	1.29	3.46	1.31
Transac-1	7:16	3.66	2.56	1.31	1.3	3.36	1.35
Transac-1	7:19	1.29	3.36	1.32	4.41	4.58	1.35
Transac-1	7:23	3.48	1.34	1.29	4.55	6.48	1.34
Transac-1	7:26	3.35	1.3	1.5	1.31	4.36	1.39
Transac-1	7:29	3.4	3.44	1.29	1.49	4.35	1.31
Transac-1	7:33	3.32	1.54	1.3	4.51	3.45	1.31
Transac-1	7:36	1.33	5.51	1.32	10.56	6.4	1.6
Transac-1	7:39	1.3	6.56	1.3	1.33	4.37	1.3
Transac-1	7:43	1.33	1.36	1.4	1.35	15.64	1.31
Transac-1	7:46	3.65	4.64	1.54	1.31	6.51	1.38
Transac-1	7:49	1.32	26.71	1.42	36.94	0	1.34
Transac-1	7:53	4.45	1.31	1.3	8.5	86.48	1.32
Transac-1	7:56	3.38	5.4	2.56	2.46	0	1.8
Transac-1	7:59	2.43	1.31	2.37	26.98	88	1.29
Transac-1	8:03	5.54	1.31	2.36	1.4	0	1.47
Transac-1	8:06	1.5	1.33	1.31	1.31	52.07	1.34
Transac-1	8:09	2.53	1.34	1.35	1.3	65.26	1.35
Transac-1	8:13	4.38	3.32	1.32	1.32	27.84	1.32
Transac-1	8:16	5.43	4.37	1.36	1.32	33.81	1.31
Transac-1	8:19	3.54	3.38	1.36	1.36	27	1.42
Transac-1	8:23	4.43	1.4	1.4	5.62	11.56	1.35
Transac-1	8:26	1.39	1.36	1.37	1.32	18.67	1.32
Transac-1	8:29	1.41	1.56	2.74	1.52	21.8	1.37
Transac-1	8:33	4.58	8.73	1.35	1.4	6.42	1.33
Transac-1	8:36	4.48	1.37	2.55	5.4	8.41	1.29
Transac-1	8:39	5.6	3.42	1.3	5.46	7.39	2.34
Transac-1	8:43	4.47	3.33	1.34	1.56	9.42	6.36
Transac-1	8:46	4.48	1.35	1.37	4.36	5.61	1.3
Transac-1	8:49	1.4	4.34	1.56	4.39	4.34	1.5
Transac-1	8:53	4.67	4.46	1.31	1.41	5.46	1.28
Transac-1	8:56	4.59	4.43	1.3	1.5	4.39	1.29
Transac-1	8:59	1.56	4.55	1.33	1.32	4.55	1.3
Transac-1	9:03	1.4	3.37	1.32	1.31	10.45	1.42
Transac-1	9:06	3.39	7.4	1.58	4.36	6.36	1.39
Transac-1	9:09	3.35	3.34	1.31	4.33	5.4	1.34
Transac-1	9:13	4.44	3.4	1.36	4.35	5.52	1.33
Transac-1	9:16	1.49	20.98	1.52	1.36	5.4	6.57
Transac-1	9:19	1.33	3.34	1.38	4.36	13.51	1.55
Transac-1	9:23	1.31	69.31	1.45	4.35	5.43	1.34
Transac-1	9:26	1.33	5.5	1.31	1.33	7.49	3.6
Transac-1	9:29	1.32	4.36	1.54	1.5	13.64	1.35
Transac-1	9:33	1.35	4.59	1.38	5.39	4.51	1.37
Transac-1	9:36	4.4	3.5	1.31	7.54	3.42	9.46
Transac-1	9:39	4.47	3.57	1.49	4.4	14.58	1.33
Transac-1	9:43	1.37	7.46	1.31	1.32	4.47	1.71
Transac-1	9:46	6.41	80.44	1.33	1.29	15.5	1.33
Transac-1	9:49	4.46	3.38	1.35	1.41	7.53	1.54
Transac-1	9:53	3.57	3.38	1.32	1.31	5.45	1.47
Transac-1	9:56	1.32	4.58	1.35	1.28	4.35	1.31
Transac-1	9:59	1.3	68.38	1.29	1.32	4.35	1.31
Transac-1	10:03	1.36	4.44	1.32	1.32	7.4	1.35
Transac-1	10:06	3.43	3.33	1.37	1.36	4.34	1.58
Transac-1	10:09	4.47	3.39	1.33	4.36	3.48	1.46
Transac-1	10:13	1.31	3.36	1.33	1.58	6.38	1.31
Transac-1	10:16	4.38	85.5	1.32	1.31	4.34	1.33
Transac-1	10:19	1.3					1.3
Transac-1	10:23
Transac-1	10:26
Transac-1	10:29
Transac-1	10:33
Transac-1	10:36
Transac-1	10:39
Transac-1	10:43
Transac-1	10:46
Transac-1	10:49
Transac-1	10:53
Transac-1	10:56
Transac-1	10:59
Transac-1	11:03
Transac-1	11:06
Transac-1	11:09
Transac-1	11:13
Transac-1	11:16
Transac-1	11:19
Transac-1	11:23
Transac-1	11:26
Transac-1	11:29
Transac-1	11:33
Transac-1	11:36
Transac-1	11:39
Transac-1	11:43
Transac-1	11:46
Transac-1	11:49
Transac-1	11:53
Transac-1	11:56
Transac-1	11:59
Transac-1	12:03
Transac-1	12:06
Transac-1	12:09
Transac-1	12:13
Transac-1	12:16
Transac-1	12:19
Transac-1	12:23
Transac-1	12:26
Transac-1	12:29
Transac-1	12:33
Transac-1	12:36
Transac-1	12:39
Transac-1	12:43
Transac-1	12:46
Transac-1	12:49
Transac-1	12:53
Transac-1	12:56
Transac-1	12:59
Transac-1	1:03
Transac-1	1:06
Transac-1	1:09
Transac-1	1:13
Transac-1	1:16
Transac-1	1:19
Transac-1	1:23
Transac-1	1:26
Transac-1	1:29
Transac-1	1:33
Transac-1	0:36
Transac-1	1:39
Transac-1	1:43
Transac-1	1:46
Transac-1	1:49
Transac-1	1:53
Transac-1	1:56
Transac-1	1:59
Transac-1	2:03
	2:06
	2:09
	2:13
	2:16
	2:19
	2:23
	2:26
	2:29
	2:33
	2:36
	2:39
	2:43
	2:46
	2:49
	2:53
	2:56
	2:29
	3:03
	3:06
	3:09
	3:13
	3:16
	3:19
	3:23
	3:26
	3:29
	3:33
	3:36
	3:39
	3:43
	3:46
	3:49
	3:53
	3:56
	3:59
	4:03
	4:06
	4:09
	4:13
	4:16
	4:19
	4:23
	4:26
	4:29
	4:33
	4:36
	4:39
	4:43
	4:46
	4:49

<tbody>
</tbody>

Thanks.

 

[frank_AL]

I can provide you formulas to answer your first 3 questions.
To start with, I Inserted 4 Rows at the top of the spreadsheet so that I could enter formulas there for Min/Max/Average and the # of transactions > 5 seconds.
This resulted in the first row of actual data being in row 6.

I started with question 3 since this conversion needs to occur first.
3. Where we have more than 60 seconds convert it to minutes and seconds?

This could be accomplished with a VBA code module to convert the cells where the value is > 60 and leave all data in their current columns/rows.
However, to do this with a formula I added columns to the right of your current data.
In cell J6 I entered this formula: =IF(C6>60,C6/60,C6)
Copied that formula across to column O and then copied the formulas in J6:O6 down all rows.


1. Is there a way to find min, max average values for a given date? For example, for 08-22 find min,max and average transaction times.
To find the min: In cell J1 I entered formula: =MIN(J6:J65)
To find the max: In cell J2 I entered formula: =MAX(J6:J65)
To find the Average: In cell J3 I entered formula: =AVERAGE(J6:J65)

2. Is there a formula that would help us find how many transactions ran longer than 5 seconds on a given day?
In cell J4 I entered formula: =COUNTIF(J6:J65,">5")

Not sure what 3 values you are looking to average in question 4 so please provide more explanation of what you are looking to accomplish here.