formula result #value! needs to equal zero for average calculation

[Guest]

the lowdown:

column A and C are start and stop times. column b is the difference,
calculating minutes to a result. If there is is only one value in column A or
C I get the #value! error in column B.

In order to calculate a correct average resolution time I need to those
#value! errors to equal zero so they are disregarded by my average
calculation.

Here is my formula:
=SUM(G2:G128)/COUNTIF(G2:G128,"<>0")

Thanks for your help!

 

[Guest]

Actually, zeros will impact your AVERAGE.

Try something like this:

For times, or blanks, in Col_A and Col_B

C1: =IF(COUNTA(A1:B1)=2,B1-A1)
copy that formula down as far a needed

The cells that calculate to FALSE will be ignored by the AVERAGE function.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro

 

[Guest]

One of these array formulas may be what you're looking for:

Using Start and End times in Col_A and Col_B, respectively, and the
diffenences in Col_C

This one calculates the differences and the average all at once:
C11: =AVERAGE(IF(ISNUMBER(A1:A10)*ISNUMBER(B1:B10),(B1:B10-A1:A10)))

This one averages the differences, excluding error values:
C11: =AVERAGE(IF(ISNUMBER(C1:C10),C1:C10))

Note: Commit those formulas by holding down the [Ctrl][Shift] keys and press
[Enter].

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro

 

[Bob Phillips]

How about doing it directly on the times

=AVERAGE(IF(((A2:A128<>"")*(C2:C128<>"")),C2:C128-A2:A128))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)