Formula Averageifs to ignore zero and negative time

[Lukma]

Hi guys

Can some help out with this formula i was trying to find the average of a time for every week, based on multiple criteria, but i need to ignore the negative time and blank or zero in cell so i am wondering if my formula is right in column C3

ILSP_Performance Tracking Data update.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
2	YES
3	1	8:33
4	Weekly	Monthly Mus End Date	Mus-No	Supply Vessel			FWB Date & Time	Manifest Date & Time	Ms't on Time	ADNOC Permission	Arrive Date & Time	Commence Cargo OP	Ex Trailer Delay Time	Complete Cargo OP	Vessel Depart AD-Jetty	W/ ADL&S To Grant Permission	W/ ADL&S To Start Cargo	Total Hrs from Arrival complete	Exceeded Hrs KPI
5	1	01-Jan-21	Mus-3705	ADNOC-810	Half	2	31/12/20 21:00	31/12/20 11:05	YES	31/12/20 21:34	01/01/21 00:50	01/01/21 03:00		01/01/21 09:00	01/01/21 09:30	10:29	2:10	8:10	-03:50
6	53	01-Dec-20	Mus-3706	ADNOC-224	Half	5	31/12/20 00:45	31/12/20 11:30	NO	31/12/20 19:09	31/12/20 23:00	01/01/21 04:00		01/01/21 18:25	01/01/21 18:48	7:39	5:0	19:25	7:25
7	1	01-Jan-21	3666-O	ASL SWIFT	Half	2	01/01/21 06:30	Off-Hire	NO		01/01/21 11:30	01/01/21 11:40		02/01/21 03:40	02/01/21 16:00		0:10	16:10	4:10
8	1	01-Jan-21	Mus-3707	Z-POWER	Half	3	30/12/20 21:25	30/12/20 22:00	NO	31/12/20 22:08	01/01/21 01:15	01/01/21 03:00		01/01/21 11:25	01/01/21 19:00	24:8	1:45	10:10	-01:50
9	1	01-Jan-21	Mus-3708	SMIT LUZON	Full	1	31/12/20 22:42	31/12/20 13:35	YES	01/01/21 04:14	01/01/21 08:30	01/01/21 08:42		01/01/21 19:54	01/01/21 20:30	14:39	0:12	11:24	-00:36
10	1	01-Jan-21	Mus-3709	SMIT LUMUT	Half	3	31/12/20 22:30	31/12/20 18:00	YES	31/12/20 23:00	01/01/21 05:20	01/01/21 07:58		01/01/21 17:45	01/01/21 19:15	5:0	2:38	12:25	0:25
11	1	01-Jan-21	Mus-3710	B-LIBERTY-313	NAF	NAF	01/01/21 05:30	31/12/20 19:30	YES	01/01/21 11:30	01/01/21 15:09	02/01/21 00:00		02/01/21 12:05	02/01/21 12:45	16:0	8:51	20:56	8:56
12	1	01-Jan-21	Mus-3711	MAC PHOENIX	Half	2	31/12/20 21:42	31/12/20 20:35	YES	01/01/21 05:05	01/01/21 09:45	01/01/21 13:30		01/01/21 20:24	01/01/21 21:22	8:30	3:45	10:39	-01:21
13	1	01-Jan-21	Mus-3712	ADNOC-812	Half	2	01/01/21 01:30	31/12/20 21:00	YES	01/01/21 04:45	01/01/21 08:05	01/01/21 08:25		01/01/21 20:25	01/01/21 21:15	7:45	0:20	12:20	0:20
14	1	01-Jan-21	Mus-3713	A-HERCULES	Half	3	01/01/21 04:00	31/12/20 21:30	YES	01/01/21 09:58	01/01/21 13:50	01/01/21 15:45		01/01/21 23:40	02/01/21 00:30	12:28	1:55	9:50	-02:10
15	1	01-Jan-21	Mus-001	LCT-TARFFAH	Full	1	02/01/21 11:00	01/01/21 11:45	YES	02/01/21 12:39	02/01/21 16:00	02/01/21 20:00		03/01/21 05:45	03/01/21 07:10	24:54	4:0	13:45	1:45
16	2	01-Jan-21	Mus-002	ADNOC-850	Half	2	02/01/21 20:10	01/01/21 13:30	YES	02/01/21 20:35	03/01/21 00:15	03/01/21 03:25		03/01/21 12:45	03/01/21 13:15	31:5	3:10	12:30	0:30
17	1	01-Jan-21	Mus-003	ADNOC-510	Full	1	01/01/21 03:24	01/01/21 12:52	NO	01/01/21 19:05	02/01/21 00:00	02/01/21 00:15		02/01/21 14:00	02/01/21 14:20	6:13	0:15	14:0	2:0
18	1	01-Jan-21	Mus-004	A-GRACE	Full	1	01/01/21 05:02	01/01/21 13:38	NO	01/01/21 17:06	01/01/21 21:40	01/01/21 23:50		02/01/21 05:35	02/01/21 06:15	3:28	2:10	7:55	-04:05
19	1	01-Jan-21	Mus-005	MARCAP-2	Full	1	01/01/21 21:30	01/01/21 14:15	YES	02/01/21 04:44	02/01/21 08:30	02/01/21 09:25		02/01/21 17:30	02/01/21 17:35	14:29	0:55	9:0	-03:00
20	1	01-Jan-21	Mus-006	QMS DELTA	Half	2	01/01/21 10:30	01/01/21 16:20	NO	01/01/21 21:00	02/01/21 01:00	02/01/21 03:40		02/01/21 21:30	02/01/21 21:50	4:40	2:40	20:30	8:30
21	1	01-Jan-21	Mus-007	ADNOC-1011	Half	3	31/12/20 21:45	01/01/21 17:00	NO	02/01/21 05:52	02/01/21 10:25	02/01/21 15:35		03/01/21 02:20	03/01/21 03:40	12:52	5:10	15:55	3:55
22	1	01-Jan-21	Mus-008	QMS NEPTUNE	Half	3	01/01/21 09:50	01/01/21 17:20	NO	01/01/21 19:14	02/01/21 00:15	02/01/21 05:20		02/01/21 18:10	02/01/21 19:00	1:54	5:5	17:55	5:55
23	1	01-Jan-21	Mus-009	ADNOC-221	Half	4	01/01/21 17:20	01/01/21 17:40	NO	01/01/21 19:30	01/01/21 23:55	02/01/21 00:30		02/01/21 23:05	03/01/21 00:45	1:50	0:35	23:10	11:10
24	1	01-Jan-21	Mus-010	SEACOR ALPS	Full	1	01/01/21 22:30	01/01/21 19:00	YES	01/01/21 22:40	02/01/21 03:50	02/01/21 04:50		03/01/21 14:00	03/01/21 14:10	3:40	1:0	34:10	22:10
25	1	01-Jan-21	Mus-011	ADNOC-229	NAF	NAF	01/01/21 16:30	01/01/21 22:10	NO	01/01/21 16:30	01/01/21 17:30	02/01/21 00:00		02/01/21 22:08	02/01/21 23:00	-05:40	6:30	28:38	16:38
26	1	01-Jan-21	Mus-012	LCT-MARWAH-1	Full	1	02/01/21 05:00	02/01/21 09:20	NO	02/01/21 09:35	02/01/21 13:05	02/01/21 15:45		03/01/21 00:10	03/01/21 00:50	0:15	2:40	11:5	-00:55
27	1	01-Jan-21	Mus-013	Z-OCEAN	Full	1	02/01/21 04:00	02/01/21 08:27	NO	02/01/21 08:48	02/01/21 12:18	02/01/21 12:54		02/01/21 17:30	02/01/21 17:30	0:21	0:36	5:12	-06:48
28	2	01-Jan-21	Mus-014	ADNOC-230	NAF	NAF	02/01/21 22:15	02/01/21 13:19	YES	03/01/21 00:45	03/01/21 04:00	03/01/21 04:25		04/01/21 05:30	04/01/21 16:30	11:26	0:25	25:30	13:30
29	1	01-Jan-21	Mus-015	A-LIBERTY	Full	1	02/01/21 12:30	02/01/21 13:55	NO	02/01/21 18:30	02/01/21 22:20	02/01/21 22:20		03/01/21 04:55	03/01/21 06:00	4:35	0:0	6:35	-05:25
30	2	01-Jan-21	Mus-016	ADNOC-851	Full	1	02/01/21 23:05	03/01/21 14:20	NO	03/01/21 00:46	03/01/21 04:00	03/01/21 07:50		03/01/21 16:05	03/01/21 16:30	-13:34	3:50	12:5	0:5
31	2	01-Jan-21	Mus-017	ADNOC-225	NAF	NAF	02/01/21 21:45	02/01/21 20:50	YES	02/01/21 22:10	03/01/21 02:00	03/01/21 03:50		03/01/21 23:59	04/01/21 00:45	1:20	1:50	21:59	9:59
32	1	01-Jan-21	Mus-018	A-CHLOE	Full	1	02/01/21 17:00	02/01/21 16:55	YES	02/01/21 18:20	02/01/21 22:30	03/01/21 00:50		03/01/21 11:30	03/01/21 13:30	1:25	2:20	13:0	1:0
Sheet1

Cell Formulas
Range	Formula
C3	C3	=AVERAGEIFS(Q5:Q32,J5:J32,"<>0"&B2,B5:B32,B3)
B5:B32	B5	=IF(L5<>"",WEEKNUM(L5,1),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I5:I32	Cell Value	contains "Maintenance"	text	NO

 

[etaf]

not sure exactly what you are doing
=AVERAGEIFS(Q5:Q32,J5:J32,"<>0"&B2,B5:B32,B3)

So get the average of Q5:Q32
Using criteria
Check if column J5;J32 where does NOT = 0 and YES
"<>0"&B2. NOT sure what you need here
And B5: B32 contains B3 = 1
you say

but i need to ignore the negative time and blank or zero in cell

What Cells give the range you are trying to ignore negative time and zero
IS this in column Q?

Please explain what you need with specific cell ranges

IE
Average of values in column Q
Where Column J = B2 (YES)
Where Column B = B3 (1)
AND values in column Q exclude any negative or zero value

Not sure how you are showing a negative time ?
Excel usually does not like that

You example does not show empty - blank cells or negative time

 

[Lukma]

Hi Etaf

Well Am Trying to find the average from column Q5 based on Criteria of YES in Column J5 and column B5 as Week
But i don't need to include negative sign and blank cell in column Q5 when looking for the average

 

[etaf]

=AVERAGEIF(Q5:Q32,"<>0",Q5:Q32)
"<>0" will ignore blanks and zeros

Average of values in column Q
Where Column J = B2 (YES)
Where Column B = B3 (1)
AND values in column Q exclude any negative or zero value

=AVERAGEIFS(Q5:Q32,Q5:Q32,"<>0",J5:J32,B2,B5:B32,B3)

Still not sure what you mean by Negative Values ????
Can you show examples

Sample set - time issue-ETAF.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
2	YES
3	1	10:50:49
4	Weekly	Monthly Mus End Date	Mus-No	Supply Vessel			FWB Date & Time	Manifest Date & Time	Ms't on Time	ADNOC Permission	Arrive Date & Time	Commence Cargo OP	Ex Trailer Delay Time	Complete Cargo OP	Vessel Depart AD-Jetty	W/ ADL&S To Grant Permission	W/ ADL&S To Start Cargo	Total Hrs from Arrival complete	Exceeded Hrs KPI
5	1	1/1/21	3705	ADNOC-810	Half	2	44196.875	44196.4618	YES	44196.8986	44197.0347	44197.125		44197.375	44197.3958	10:29:00	0.09027778	0.34027778	0
6	53	12/1/20	3706	ADNOC-224	Half	5	44196.0313	44196.4792	NO	44196.7979	44196.9583	44197.1667		44197.7674	44197.7833	07:39:00	0.20833333	0.80902778	0.30902778
7	1	1/1/21	3666-O	ASL SWIFT	Half	2	44197.2708	Off-Hire	NO		44197.4792	44197.4861		44198.1528	44198.6667		0.00694444	0.67361111	0.17361111
8	1	1/1/21	3707	Z-POWER	Half	3	44195.8924	44195.9167	NO	44196.9222	44197.0521	44197.125		44197.4757	44197.7917	00:08:00	0.07291667	0.42361111	0
9	1	1/1/21	3708	SMIT LUZON	Full	1	44196.9458	44196.566	YES	44197.1764	44197.3542	44197.3625		44197.8292	44197.8542	14:39:00	0.00833333	0.475	-00:36
10	1	1/1/21	3709	SMIT LUMUT	Half	3	44196.9375	44196.75	YES	44196.9583	44197.2222	44197.3319		44197.7396	44197.8021	05:00:00	0.10972222	0.51736111	0.01736111
11	1	1/1/21	3710	B-LIBERTY-313	NAF	NAF	44197.2292	44196.8125	YES	44197.4792	44197.6313	44198		44198.5035	44198.5313	16:00:00	0.36875	0.87222222	0.37222222
12	1	1/1/21	3711	MAC PHOENIX	Half	2	44196.9042	44196.8576	YES	44197.2118	44197.4063	44197.5625		44197.85	44197.8903	08:30:00	0.15625	0.44375	0
13	1	1/1/21	3712	ADNOC-812	Half	2	44197.0625	44196.875	YES	44197.1979	44197.3368	44197.3507		44197.8507	44197.8854	07:45:00	0.01388889	0.51388889	0.01388889
14	1	1/1/21	3713	A-HERCULES	Half	3	44197.1667	44196.8958	YES	44197.4153	44197.5764	44197.6563		44197.9861	44198.0208	12:28:00	0.07986111	0.40972222	0
15	1	1/1/21	1	LCT-TARFFAH	Full	1	44198.4583	44197.4896	YES	44198.5271	44198.6667	44198.8333		44199.2396	44199.2986	00:54:00	0.16666667	0.57291667	0.07291667
16	2	1/1/21	2	ADNOC-850	Half	2	44198.8403	44197.5625	YES	44198.8576	44199.0104	44199.1424		44199.5313	44199.5521	07:05:00	0.13194444	0.52083333	0.02083333
17	1	1/1/21	3	ADNOC-510	Full	1	44197.1417	44197.5361	NO	44197.7951	44198	44198.0104		44198.5833	44198.5972	06:13:00	0.01041667	0.58333333	0.08333333
18	1	1/1/21	4	A-GRACE	Full	1	44197.2097	44197.5681	NO	44197.7125	44197.9028	44197.9931		44198.2326	44198.2604	03:28:00	0.09027778	0.32986111	0
19	1	1/1/21	5	MARCAP-2	Full	1	44197.8958	44197.5938	YES	44198.1972	44198.3542	44198.3924		44198.7292	44198.7326	14:29:00	0.03819444	0.375	-03:00
20	1	1/1/21	6	QMS DELTA	Half	2	44197.4375	44197.6806	NO	44197.875	44198.0417	44198.1528		44198.8958	44198.9097	04:40:00	0.11111111	0.85416667	0.35416667
21	1	1/1/21	7	ADNOC-1011	Half	3	44196.9063	44197.7083	NO	44198.2444	44198.434	44198.6493		44199.0972	44199.1528	12:52:00	0.21527778	0.66319444	0.16319444
22	1	1/1/21	8	QMS NEPTUNE	Half	3	44197.4097	44197.7222	NO	44197.8014	44198.0104	44198.2222		44198.7569	44198.7917	01:54:00	0.21180556	0.74652778	0.24652778
23	1	1/1/21	9	ADNOC-221	Half	4	44197.7222	44197.7361	NO	44197.8125	44197.9965	44198.0208		44198.9618	44199.0313	01:50:00	0.02430556	0.96527778	0.46527778
24	1	1/1/21	10	SEACOR ALPS	Full	1	44197.9375	44197.7917	YES	44197.9444	44198.1597	44198.2014		44199.5833	44199.5903	03:40:00	0.04166667	1.42361111	0.92361111
25	1	1/1/21	11	ADNOC-229	NAF	NAF	44197.6875	44197.9236	NO	44197.6875	44197.7292	44198		44198.9222	44198.9583	00:00:00	0.27083333	1.19305556	0.69305556
26	1	1/1/21	12	LCT-MARWAH-1	Full	1	44198.2083	44198.3889	NO	44198.3993	44198.5451	44198.6563		44199.0069	44199.0347	00:15:00	0.11111111	0.46180556	-00:55
27	1	1/1/21	13	Z-OCEAN	Full	1	44198.1667	44198.3521	NO	44198.3667	44198.5125	44198.5375		44198.7292	44198.7292	00:21:00	0.025	0.21666667	0
28	2	1/1/21	14	ADNOC-230	NAF	NAF	44198.9271	44198.5549	YES	44199.0313	44199.1667	44199.184		44200.2292	44200.6875	11:26:00	0.01736111	1.0625	0.5625
29	1	1/1/21	15	A-LIBERTY	Full	1	44198.5208	44198.5799	NO	44198.7708	44198.9306	44198.9306		44199.2049	44199.25	04:35:00	0	0.27430556	0
30	2	1/1/21	16	ADNOC-851	Full	1	44198.9618	44199.5972	NO	44199.0319	44199.1667	44199.3264		44199.6701	44199.6875	00:00:00	0.15972222	0.50347222	0.00347222
31	2	1/1/21	17	ADNOC-225	NAF	NAF	44198.9063	44198.8681	YES	44198.9236	44199.0833	44199.1597		44199.9993	44200.0313	01:20:00	0.07638889	0.91597222	0.41597222
32	1	1/1/21	18	A-CHLOE	Full	1	44198.7083	44198.7049	YES	44198.7639	44198.9375	44199.0347		44199.4792	44199.5625	01:25:00	0.09722222	0.54166667	0.04166667
33
34
35																08:33:31
36																09:14:36
Sheet2

Cell Formulas
Range	Formula
C3	C3	=AVERAGEIFS(Q5:Q32,Q5:Q32,"<>0",J5:J32,B2,B5:B32,B3)
Q35	Q35	=AVERAGE(Q5:Q32)
Q36	Q36	=AVERAGEIF(Q5:Q32,">0",Q5:Q32)

 

[Lukma]

Hi Etaf

Thanks well well to solve my issue can i ask if am using Averageifs can it ignore Zero and Negative sign ?

 

[etaf]

as mentioned ,

What Cells give the range you are trying to ignore negative time

Not sure how you are showing a negative time ?
Excel usually does not like that

You example does not show .............. negative time

Still not sure what you mean by Negative Values ????

i dont understand the negative times
Can you explain and show the examples of where you use negative time

For negative values , blanks & zero - then you would need to use ">0"