Finding Average Time Excluding Zero

JoeH7745 · Saturday at 9:48 AM

I have a worksheet where i have a start and finish date and time in separate columns. I have another column that has a formula to give me the time in hours and minutes it took from start to finish. I need to average the completion time of the column W3:W412 and exclude the 0:00 times for the cells in the column that do not have data yet. I get a #DIVO/0! error each way I try it.

DanteAmor · Saturday at 11:48 AM

Try

Excel Formula:

=IFERROR(AVERAGEIF(W3:W412,"<>0",W3:W412),0)

6StringJazzer · Saturday at 11:48 AM

I cannot reproduce your result, but I don't have all of your data. You will get a #DIV/0! error if all of the times are 0:00. That is true of the data we can see, but of course we have no idea what comes after that up to row 412.

For your version of Excel the following formula is more elegant, but you may get the same error.

Excel Formula:

=AVERAGEIF(W3:W412,"<>0")

JoeH7745 · Saturday at 2:16 PM

DanteAmor said:
Try

Excel Formula:

=IFERROR(AVERAGEIF(W3:W412,"<>0",W3:W412),0)

I tried this and it returns a 0:00, I am using X3:X412 because there are times there other than 0:00

JoeH7745 · Saturday at 2:20 PM

6StringJazzer said:
I cannot reproduce your result, but I don't have all of your data. You will get a #DIV/0! error if all of the times are 0:00. That is true of the data we can see, but of course we have no idea what comes after that up to row 412.

For your version of Excel the following formula is more elegant, but you may get the same error.

Excel Formula:

=AVERAGEIF(W3:W412,"<>0")

JoeH7745 · Saturday at 2:21 PM

FROM ABOUT 152 TO 412 THERE IS NO DATA OR 0:00 BUT 3 TO 151 HAS TIMES RANGING FROM 0:15 TO 4:04. This formula still gices me an error

DanteAmor · Saturday at 5:27 PM

JoeH7745 said:
I tried this and it returns a 0:00, I am using X3:X412 because there are times there other than 0:00

Works for me:

DANTE AMOR

W

X

Y

Z

AA

AB

1

2

TOT CSG

TOT LAST

3

0:00

00:54

4

0:00

00:52

5

0:00

00:13

AVE CSG

00:00

6

0:00

00:22

AVE COM

00:35

7

Hoja1

Cell Formulas
Range	Formula
W3:W6	W3	=T3-U3
AA5	AA5	=IFERROR(AVERAGEIF(W3:W412,"<>0",W3:W412),0)
AA6	AA6	=IFERROR(AVERAGEIF(X3:X412,"<>0",X3:X412),0)

Check that the cells in columns W and X really have numbers.

JoeH7745 · Sunday at 4:25 PM

MASTER TRACKER 1.xlsx

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

AB

AC

AD

AE

1

1st QTR LATSUPS

2nd QTR LATSUPS

3rd QTR LATSUPS

4th QTR LATSUPS

TOTAL ANNUAL

TOTAL NOT SUPPORTED

2

INITIATED DATE

DOC UIC

DOC #

SQUADRON

TMS

BUNO

NOMENCLATURE

COG

NIIN

QTY

PROJ CODE

DOCUMENT STATUS

DATE / TIME SENT TO ILS OR CSG

DATE / TIME RECEIVED FROM ILS OR CSG

DECISION

REASON CODE

ISSUING UNITS RIC

CNAL REMARKS

COMPLETION DATE/TIME

DATE SHIPPED

DATE CUSTOMER RECEIVED

ALLOWANCE

TOTAL ILS OR CSG TIME

TOTAL LATSUP TIME COMPLETION TIME

MOST LATSUP'd NIIN

ASD MOST FREQUENTLY REQUESTING LATSUPS

ASD MOST FREQUENTLY SUPPORTING LATSUPS

3

10/1/24 10:06

N44329

4263GA71

VAQ-129

EA-18G

N/A

9B

01-340-9143

2

AK0

BBSMS

APPROVED

P23 - ASD LEMOORE

10/1/24 11:00

N/A

4

10/2/24 9:23

N4339A

4270GC59

VFA-213

FA-18E

N/A

9B

01-443-4215

1

AK0

BBSMS

APPROVED

NA3 - ASD IWAKUNI

10/2/24 10:15

N/A

0:00

0:52

5

10/7/24 14:22

N44329

4275GA75

VAQ-129

EA-18G

N/A

9B

01-546-6220

1

AK0

BBSMS

APPROVED

NA3 - ASD IWAKUNI

10/7/24 14:35

N/A

0:00

0:13

AVERAGE ILS CSG TIME

0.00

APPROVED

1. Multiple AK0's

P28 - ASD FALLON

6

10/3/24 10:23

N44321

4275GC67

VFA-213

FA-18E

N/A

9B

01-468-1523

1

AK0

BBSMS

APPROVED

PKZ - ASD WHIDBEY

10/3/24 10:45

N/A

0:00

0:22

AVERAGE COMPETION TIME

0.00

NOT SUPPORTED

2. System filled / Wholesale available

NVN - FT WORTH

7

10/4/24 11:40

N44329

4275GA78

VAQ-129

EA-18G

N/A

9B

01-465-2449

6

AKO

BBSMS

APPROVED

NA3 - ASD IWAKUNI

10/4/24 12:35

N/A

0:00

0:55

AWT CSG

3. No assets avail

NA3 - ASD IWAKUNI

8

10/8/24 12:20

N4339A

4276GR38

VFA-106

FA-18F

N/A

9B

01-454-3039

1

AK0

BBSMS

APPROVED

PKZ - ASD WHIDBEY

10/8/24 13:01

N/A

0:00

0:41

AWT ILS

4. SARDIP filled

PJZ - ASD JACKSONVILLE

9

10/9/24 8:11

N44329

4281GC28

VAQ-142

EA-18G

N/A

9B

01-475-6528

1

AK0

BBSMS

APPROVED

P23 - ASD LEMOORE

10/9/24 8:32

N/A

0:00

0:21

HIGHER LEVEL REVIEW

5. DOC not in system or AMCR

QCZ - ASD KEY WEST

10

10/9/24 8:17

N44329

4268GT62

VAQ-142

EA-18G

N/A

9B

01-340-9143

1

AK7

BBSMS

APPROVED

P23 - ASD LEMOORE

10/9/24 8:36

N/A

0:00

0:19

IN REVIEW

6. MOC priority overried

P23 - ASD LEMOORE

11

10/9/24 11:25

N44329

4278GT03

VAQ-135

EA-18G

N/A

9B

01-478-2887

12

AK0

BVSMS

APPROVED

P28 - ASD FALLON

10/9/24 11:43

N/A

0:00

0:18

7. Low assets/high-demand or deployed ships/unit

P29 - ASD MAYPORT

12

10/9/24 11:30

N52994

4280GP55

VFC-12

FA-18F

N/A

3B

01-536-7073

1

AK0

BVSMS

APPROVED

P23 - ASD LEMOORE

10/9/24 12:22

N/A

0:00

0:52

8. Part repaired by SQD/FRC

P56 - ASD MISAWA

13

10/10/24 8:12

N44329

4263G678

VAQ-136

EA-18G

N/A

7R

01-529-7073

1

AK0

BBNRP

APPROVED

NA3 - ASD IWAKUNI

10/10/24 9:30

N/A

0:00

1:18

9. Alternate NIIN avail

PNZ - ASD NORFOLK

14

10/11/24 7:36

N44329

4260GT43

VAQ-135

EA-18G

N/A

7R

01-522-1457

1

AK0

BBNRP

APPROVED

P23 - ASD LEMOORE

10/11/24 8:03

N/A

0:00

0:27

10. Part inaccessible (inventory, crane, & etc.)

PDZ - ASD NORTH ISLAND

15

10/15/24 10:45

N44329

4134GA28

VAQ-129

EA-18G

N/A

3B

01-536-7073

1

AK0

BVSMS

NOT SUPPORTED

7. Low assets/high-demand or deployed ships/unit

HOLDING 2 ASSETS FOR DEPLOYERS, TRACKING 11 OF THIS NIIN ON CONSOLIDATED REPORT

10/15/24 12:00

N/A

0:00

1:15

11. Document Cancelled

P0Z - ASD OCEANA

16

10/16/24 7:19

N44329

4281G615

VAQ-136

EA-18G

N/A

7R

01-546-4160

1

AK0

BBNRP

APPROVED

NA3 - ASD IWAKUNI

10/16/24 7:45

N/A

0:00

0:26

PRZ - ASD PAX

17

10/16/24 10:38

N44329

4278GP10

VAQ-132

EA-18G

N/A

7R

01-522-6448

1

AK0

BBNRP

APPROVED

P23 - ASD LEMOORE

10/16/24 10:45

N/A

0:00

0:07

PKZ - ASD WHIDBEY

18

10/17/24 11:39

N4339A

4289GR40

VFA-106

FA-18F

N/A

9B

01-454-5715

1

AK0

BBSMS

NOT SUPPORTED

7. Low assets/high-demand or deployed ships/unit

HOLDING 2 ASSETS IN IWAKUNI FOR DEPLOYERS,

10/17/24 13:30

N/A

0:00

1:51

P64 - CRANE

19

10/17/24 11:53

N44329

4284GL44

VAQ-138

EA-18G

N/A

9B

01-340-9143

2

AK0

BBSMS

APPROVED

P23 - ASD LEMOORE

10/17/24 12:35

N/A

0:00

0:42

R2P - NIMITZ

20

10/18/24 7:56

N44321

4284GS19

VFA-2

FA-18E

N/A

9B

00-248-3842

8

AK0

BBSMS

APPROVED

R2A - IKE

10/18/24 8:23

N/A

0:00

0:27

R2A - IKE

21

10/21/24 13:56

N4339A

4292GR23

VFA-106

FA-18F

N/A

9B

01-481-9301

1

AK0

BBSMS

APPROVED

P23 - ASD LEMOORE

10/21/24 14:00

N/A

0:00

0:04

R2Y - TR

22

10/22/24 9:44

N44321

4290G351

VX-31

FA-18F

N/A

9B

01-215-5512

1

AK0

BBSMS

APPROVED

NA3 - ASD IWAKUNI

10/22/24 10:15

N/A

0:00

0:31

R2Z - LINCOLN

23

10/22/24 11:27

N4339A

4283G973

SLM

FA-18F

N/A

7R

01-467-5264

1

AK0

BBNRP

APPROVED

R63 - P-COLA

10/22/24 12:12

N/A

0:00

0:45

R2G - GW

24

10/22/24 12:35

N4339A

4057G690

SLM

FA-18F

N/A

7R

01-531-7578

1

AK0

BBNRP

APPROVED

R2Y - TR

10/22/24 12:55

N/A

0:00

0:20

R3Q - JCS

25

10/22/24 12:35

N4339A

4123G690

SLM

FA-18F

N/A

7R

01-531-7578

1

AK0

BBNRP

APPROVED

PGG - GHWB

10/22/24 12:55

N/A

0:00

0:20

RGT - HST

26

10/22/24 12:35

N00421

4289HG34

VX-23

FA-18F

N/A

9B

01-480-5005

1

AK0

BBSMS

APPROVED

P0Z - ASD OCEANA

10/22/24 12:55

N/A

0:00

0:20

R9C - REAGAN

27

10/23/24 8:06

N44321

4292G889

NAMCE LEMOORE

FA-18F

N/A

9B

01-465-2433

10

AK0

BVSMS

APPROVED

P0Z - ASD OCEANA

10/23/24 9:11

N/A

0:00

1:05

PGG - GHWB

28

10/23/24 10:30

N4339A

4260GR36

VFA-106

FA-18F

N/A

9B

01-511-6991

1

AK0

BBSMS

APPROVED

PKZ - ASD WHIDBEY

10/23/24 10:42

N/A

0:00

0:12

RAU - GRF

29

10/23/24 12:26

N00421

4289GH35

NTPS

FA-18E

N/A

9B

01-135-3548

1

AK0

BBSMS

APPROVED

P28 - ASD FALLON

10/23/24 12:34

N/A

0:00

0:08

R63 - P-COLA

FY 25 MOC CELL 1

Cell Formulas
Range	Formula
C1	C1	=COUNTIFS($A$2:$A$412,">=10/1/2024",$A$2:$A$412,"<=12/31/2024")
G1	G1	=COUNTIFS($A$2:$A$412,">=1/1/2025",$A$2:$A$412,"<=3/31/2025")
K1	K1	=COUNTIFS($A$2:$A$412,">=4/1/2025",$A$2:$A$412,"<=6/31/2025")
O1	O1	=COUNTIFS($A$2:$A$412,">=7/1/2025",$A$2:$A$412,"<=9/30/2025")
R1	R1	=SUM(C1+G1+K1+O1)
U1	U1	=COUNTIF(O3:O142, "NOT SUPPORTED")
W3:W29	W3	=TEXT(N3-M3,"H:mm")
X3:X29	X3	=TEXT(S3-A3,"H:mm")
Y3	Y3	=MODE.MULT(I3:I412)
Z3	Z3	=INDEX(B3:B412,MODE(IF((B3:B412<>"")*ISNA(MATCH(B3:B412,$B$1:$B1,0)),MATCH(B3:B412,B3:B412,0))))
AA3	AA3	=INDEX(Q3:Q412,MODE(IF((Q3:Q412<>"")*ISNA(MATCH(Q3:Q412,$B$1:$B1,0)),MATCH(Q3:Q412,Q3:Q412,0))))
AA5	AA5	=IFERROR(AVERAGEIF(W3:W412,"<>0",W3:W412),0)
AA6	AA6	=IFERROR(AVERAGEIF(X3:X412,"<>0",X3:X412),0)
Press CTRL+SHIFT+ENTER to enter array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I3:I412	Cell Value	duplicates	text	NO
S3:V412	Cell	does not contain a blank value	text	NO
S3:V412	Cell	contains a blank value	text	NO
R4:R412	Expression	=$O4="HIGHER LEVEL REVIEW"	text	NO
R4:R412	Expression	=$O4="AWT ILS"	text	NO
R4:R412	Expression	=$O4="AWT CSG"	text	NO
R4:R412	Expression	=$O4="IN REVIEW"	text	NO
R4:R412	Expression	=$O4="NOT SUPPORTED"	text	NO
R4:R412	Expression	=$O4="APPROVED"	text	NO
R3,A3:Q412	Expression	=$O3="HIGHER LEVEL REVIEW"	text	NO
R3,A3:Q412	Expression	=$O3="AWT ILS"	text	NO
R3,A3:Q412	Expression	=$O3="AWT CSG"	text	NO
R3,A3:Q412	Expression	=$O3="IN REVIEW"	text	NO
R3,A3:Q412	Expression	=$O3="NOT SUPPORTED"	text	NO
R3,A3:Q412	Expression	=$O3="APPROVED"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
O3:O412	List	=$AC$5:$AC$10
P3:P412	List	=$AD$5:$AD$15
Q3:Q412	List	=$AE$5:$AE$29

JoeH7745 · Monday at 6:42 AM

JoeH7745 said:
MASTER TRACKER 1.xlsx
A B C D E F G H I J K L M N O P Q R S T U V W X Y Z AA AB AC AD AE
1 1st QTR LATSUPS 81 2nd QTR LATSUPS 68 3rd QTR LATSUPS 0 4th QTR LATSUPS 0 TOTAL ANNUAL 149 TOTAL NOT SUPPORTED 18
2 INITIATED DATE DOC UIC DOC # SQUADRON TMS BUNO NOMENCLATURE COG NIIN QTY PROJ CODE DOCUMENT STATUS DATE / TIME SENT TO ILS OR CSG DATE / TIME RECEIVED FROM ILS OR CSG DECISION REASON CODE ISSUING UNITS RIC CNAL REMARKS COMPLETION DATE/TIME DATE SHIPPED DATE CUSTOMER RECEIVED ALLOWANCE TOTAL ILS OR CSG TIME TOTAL LATSUP TIME COMPLETION TIME MOST LATSUP'd NIIN ASD MOST FREQUENTLY REQUESTING LATSUPS ASD MOST FREQUENTLY SUPPORTING LATSUPS
3 10/1/24 10:06 N44329 4263GA71 VAQ-129 EA-18G N/A N/A 9B 01-340-9143 2 AK0 BBSMS APPROVED P23 - ASD LEMOORE 10/1/24 11:00 N/A N/A N/A 0:00 0:54 01-640-8454 N4339A P23 - ASD LEMOORE
4 10/2/24 9:23 N4339A 4270GC59 VFA-213 FA-18E N/A N/A 9B 01-443-4215 1 AK0 BBSMS APPROVED NA3 - ASD IWAKUNI 10/2/24 10:15 N/A N/A N/A 0:00 0:52
5 10/7/24 14:22 N44329 4275GA75 VAQ-129 EA-18G N/A N/A 9B 01-546-6220 1 AK0 BBSMS APPROVED NA3 - ASD IWAKUNI 10/7/24 14:35 N/A N/A N/A 0:00 0:13 AVERAGE ILS CSG TIME 0.00 APPROVED 1. Multiple AK0's P28 - ASD FALLON
6 10/3/24 10:23 N44321 4275GC67 VFA-213 FA-18E N/A N/A 9B 01-468-1523 1 AK0 BBSMS APPROVED PKZ - ASD WHIDBEY 10/3/24 10:45 N/A N/A N/A 0:00 0:22 AVERAGE COMPETION TIME 0.00 NOT SUPPORTED 2. System filled / Wholesale available NVN - FT WORTH
7 10/4/24 11:40 N44329 4275GA78 VAQ-129 EA-18G N/A N/A 9B 01-465-2449 6 AKO BBSMS APPROVED NA3 - ASD IWAKUNI 10/4/24 12:35 N/A N/A N/A 0:00 0:55 AWT CSG 3. No assets avail NA3 - ASD IWAKUNI
8 10/8/24 12:20 N4339A 4276GR38 VFA-106 FA-18F N/A N/A 9B 01-454-3039 1 AK0 BBSMS APPROVED PKZ - ASD WHIDBEY 10/8/24 13:01 N/A N/A N/A 0:00 0:41 AWT ILS 4. SARDIP filled PJZ - ASD JACKSONVILLE
9 10/9/24 8:11 N44329 4281GC28 VAQ-142 EA-18G N/A N/A 9B 01-475-6528 1 AK0 BBSMS APPROVED P23 - ASD LEMOORE 10/9/24 8:32 N/A N/A N/A 0:00 0:21 HIGHER LEVEL REVIEW 5. DOC not in system or AMCR QCZ - ASD KEY WEST
10 10/9/24 8:17 N44329 4268GT62 VAQ-142 EA-18G N/A N/A 9B 01-340-9143 1 AK7 BBSMS APPROVED P23 - ASD LEMOORE 10/9/24 8:36 N/A N/A N/A 0:00 0:19 IN REVIEW 6. MOC priority overried P23 - ASD LEMOORE
11 10/9/24 11:25 N44329 4278GT03 VAQ-135 EA-18G N/A N/A 9B 01-478-2887 12 AK0 BVSMS APPROVED P28 - ASD FALLON 10/9/24 11:43 N/A N/A N/A 0:00 0:18 7. Low assets/high-demand or deployed ships/unit P29 - ASD MAYPORT
12 10/9/24 11:30 N52994 4280GP55 VFC-12 FA-18F N/A N/A 3B 01-536-7073 1 AK0 BVSMS APPROVED P23 - ASD LEMOORE 10/9/24 12:22 N/A N/A N/A 0:00 0:52 8. Part repaired by SQD/FRC P56 - ASD MISAWA
13 10/10/24 8:12 N44329 4263G678 VAQ-136 EA-18G N/A N/A 7R 01-529-7073 1 AK0 BBNRP APPROVED NA3 - ASD IWAKUNI 10/10/24 9:30 N/A N/A N/A 0:00 1:18 9. Alternate NIIN avail PNZ - ASD NORFOLK
14 10/11/24 7:36 N44329 4260GT43 VAQ-135 EA-18G N/A N/A 7R 01-522-1457 1 AK0 BBNRP APPROVED P23 - ASD LEMOORE 10/11/24 8:03 N/A N/A N/A 0:00 0:27 10. Part inaccessible (inventory, crane, & etc.) PDZ - ASD NORTH ISLAND
15 10/15/24 10:45 N44329 4134GA28 VAQ-129 EA-18G N/A N/A 3B 01-536-7073 1 AK0 BVSMS NOT SUPPORTED 7. Low assets/high-demand or deployed ships/unit HOLDING 2 ASSETS FOR DEPLOYERS, TRACKING 11 OF THIS NIIN ON CONSOLIDATED REPORT 10/15/24 12:00 N/A N/A N/A 0:00 1:15 11. Document Cancelled P0Z - ASD OCEANA
16 10/16/24 7:19 N44329 4281G615 VAQ-136 EA-18G N/A N/A 7R 01-546-4160 1 AK0 BBNRP APPROVED NA3 - ASD IWAKUNI 10/16/24 7:45 N/A N/A N/A 0:00 0:26 PRZ - ASD PAX
17 10/16/24 10:38 N44329 4278GP10 VAQ-132 EA-18G N/A N/A 7R 01-522-6448 1 AK0 BBNRP APPROVED P23 - ASD LEMOORE 10/16/24 10:45 N/A N/A N/A 0:00 0:07 PKZ - ASD WHIDBEY
18 10/17/24 11:39 N4339A 4289GR40 VFA-106 FA-18F N/A N/A 9B 01-454-5715 1 AK0 BBSMS NOT SUPPORTED 7. Low assets/high-demand or deployed ships/unit HOLDING 2 ASSETS IN IWAKUNI FOR DEPLOYERS, 10/17/24 13:30 N/A N/A N/A 0:00 1:51 P64 - CRANE
19 10/17/24 11:53 N44329 4284GL44 VAQ-138 EA-18G N/A N/A 9B 01-340-9143 2 AK0 BBSMS APPROVED P23 - ASD LEMOORE 10/17/24 12:35 N/A N/A N/A 0:00 0:42 R2P - NIMITZ
20 10/18/24 7:56 N44321 4284GS19 VFA-2 FA-18E N/A N/A 9B 00-248-3842 8 AK0 BBSMS APPROVED R2A - IKE 10/18/24 8:23 N/A N/A N/A 0:00 0:27 R2A - IKE
21 10/21/24 13:56 N4339A 4292GR23 VFA-106 FA-18F N/A N/A 9B 01-481-9301 1 AK0 BBSMS APPROVED P23 - ASD LEMOORE 10/21/24 14:00 N/A N/A N/A 0:00 0:04 R2Y - TR
22 10/22/24 9:44 N44321 4290G351 VX-31 FA-18F N/A N/A 9B 01-215-5512 1 AK0 BBSMS APPROVED NA3 - ASD IWAKUNI 10/22/24 10:15 N/A N/A N/A 0:00 0:31 R2Z - LINCOLN
23 10/22/24 11:27 N4339A 4283G973 SLM FA-18F N/A N/A 7R 01-467-5264 1 AK0 BBNRP APPROVED R63 - P-COLA 10/22/24 12:12 N/A N/A N/A 0:00 0:45 R2G - GW
24 10/22/24 12:35 N4339A 4057G690 SLM FA-18F N/A N/A 7R 01-531-7578 1 AK0 BBNRP APPROVED R2Y - TR 10/22/24 12:55 N/A N/A N/A 0:00 0:20 R3Q - JCS
25 10/22/24 12:35 N4339A 4123G690 SLM FA-18F N/A N/A 7R 01-531-7578 1 AK0 BBNRP APPROVED PGG - GHWB 10/22/24 12:55 N/A N/A N/A 0:00 0:20 RGT - HST
26 10/22/24 12:35 N00421 4289HG34 VX-23 FA-18F N/A N/A 9B 01-480-5005 1 AK0 BBSMS APPROVED P0Z - ASD OCEANA 10/22/24 12:55 N/A N/A N/A 0:00 0:20 R9C - REAGAN
27 10/23/24 8:06 N44321 4292G889 NAMCE LEMOORE FA-18F N/A N/A 9B 01-465-2433 10 AK0 BVSMS APPROVED P0Z - ASD OCEANA 10/23/24 9:11 N/A N/A N/A 0:00 1:05 PGG - GHWB
28 10/23/24 10:30 N4339A 4260GR36 VFA-106 FA-18F N/A N/A 9B 01-511-6991 1 AK0 BBSMS APPROVED PKZ - ASD WHIDBEY 10/23/24 10:42 N/A N/A N/A 0:00 0:12 RAU - GRF
29 10/23/24 12:26 N00421 4289GH35 NTPS FA-18E N/A N/A 9B 01-135-3548 1 AK0 BBSMS APPROVED P28 - ASD FALLON 10/23/24 12:34 N/A N/A N/A 0:00 0:08 R63 - P-COLA
FY 25 MOC CELL 1
Cell Formulas
Range Formula
C1 C1 =COUNTIFS($A$2:$A$412,">=10/1/2024",$A$2:$A$412,"<=12/31/2024")
G1 G1 =COUNTIFS($A$2:$A$412,">=1/1/2025",$A$2:$A$412,"<=3/31/2025")
K1 K1 =COUNTIFS($A$2:$A$412,">=4/1/2025",$A$2:$A$412,"<=6/31/2025")
O1 O1 =COUNTIFS($A$2:$A$412,">=7/1/2025",$A$2:$A$412,"<=9/30/2025")
R1 R1 =SUM(C1+G1+K1+O1)
U1 U1 =COUNTIF(O3:O142, "NOT SUPPORTED")
W3:W29 W3 =TEXT(N3-M3,"H:mm")
X3:X29 X3 =TEXT(S3-A3,"H:mm")
Y3 Y3 =MODE.MULT(I3:I412)
Z3 Z3 =INDEX(B3:B412,MODE(IF((B3:B412<>"")*ISNA(MATCH(B3:B412,$B$1:$B1,0)),MATCH(B3:B412,B3:B412,0))))
AA3 AA3 =INDEX(Q3:Q412,MODE(IF((Q3:Q412<>"")*ISNA(MATCH(Q3:Q412,$B$1:$B1,0)),MATCH(Q3:Q412,Q3:Q412,0))))
AA5 AA5 =IFERROR(AVERAGEIF(W3:W412,"<>0",W3:W412),0)
AA6 AA6 =IFERROR(AVERAGEIF(X3:X412,"<>0",X3:X412),0)
Press CTRL+SHIFT+ENTER to enter array formulas.
Cells with Conditional Formatting
Cell Condition Cell Format Stop If True
I3:I412 Cell Value duplicates text NO
S3:V412 Cell does not contain a blank value text NO
S3:V412 Cell contains a blank value text NO
R4:R412 Expression =$O4="HIGHER LEVEL REVIEW" text NO
R4:R412 Expression =$O4="AWT ILS" text NO
R4:R412 Expression =$O4="AWT CSG" text NO
R4:R412 Expression =$O4="IN REVIEW" text NO
R4:R412 Expression =$O4="NOT SUPPORTED" text NO
R4:R412 Expression =$O4="APPROVED" text NO
R3,A3:Q412 Expression =$O3="HIGHER LEVEL REVIEW" text NO
R3,A3:Q412 Expression =$O3="AWT ILS" text NO
R3,A3:Q412 Expression =$O3="AWT CSG" text NO
R3,A3:Q412 Expression =$O3="IN REVIEW" text NO
R3,A3:Q412 Expression =$O3="NOT SUPPORTED" text NO
R3,A3:Q412 Expression =$O3="APPROVED" text NO
Cells with Data Validation
Cell Allow Criteria
O3:O412 List =$AC$5:$AC$10
P3:P412 List =$AD$5:$AD$15
Q3:Q412 List =$AE$5:$AE$29
[/RAN

Cells with Data Validation
Cell	Allow	Criteria

JoeH7745 · Monday at 6:43 AM

I figured it out! I had a mistake in the cells counting time which threw off the average formula! Thank you for your help!

Finding Average Time Excluding Zero

JoeH7745

New Member

Attachments

DanteAmor

Well-known Member

6StringJazzer

Well-known Member

JoeH7745

New Member

Attachments

JoeH7745

New Member

JoeH7745

New Member

DanteAmor

Well-known Member

JoeH7745

New Member

JoeH7745

New Member

JoeH7745

New Member

Similar threads

Forum statistics

Share this page

Finding Average Time Excluding Zero

New Member

Attachments

Well-known Member

Well-known Member

New Member

Attachments

New Member

New Member

Well-known Member

New Member

New Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock