software

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,quot;lt;gt;0quot;)

Thanks for your help!
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-Proquot;LauraRosequot; wrote:

gt; the lowdown:
gt;
gt; column A and C are start and stop times. column b is the difference,
gt; calculating minutes to a result. If there is is only one value in column A or
gt; C I get the #value! error in column B.
gt;
gt; In order to calculate a correct average resolution time I need to those
gt; #value! errors to equal zero so they are disregarded by my average
gt; calculation.
gt;
gt; Here is my formula:
gt; =SUM(G2:G128)/COUNTIF(G2:G128,quot;lt;gt;0quot;)
gt;
gt; Thanks for your help!
gt;
gt;

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-Proquot;Ron Coderrequot; wrote:

gt; Actually, zeros will impact your AVERAGE.
gt;
gt; Try something like this:
gt;
gt; For times, or blanks, in Col_A and Col_B
gt;
gt; C1: =IF(COUNTA(A1:B1)=2,B1-A1)
gt; copy that formula down as far a needed
gt;
gt; The cells that calculate to FALSE will be ignored by the AVERAGE function.
gt;
gt; Does that help?
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;LauraRosequot; wrote:
gt;
gt; gt; the lowdown:
gt; gt;
gt; gt; column A and C are start and stop times. column b is the difference,
gt; gt; calculating minutes to a result. If there is is only one value in column A or
gt; gt; C I get the #value! error in column B.
gt; gt;
gt; gt; In order to calculate a correct average resolution time I need to those
gt; gt; #value! errors to equal zero so they are disregarded by my average
gt; gt; calculation.
gt; gt;
gt; gt; Here is my formula:
gt; gt; =SUM(G2:G128)/COUNTIF(G2:G128,quot;lt;gt;0quot;)
gt; gt;
gt; gt; Thanks for your help!
gt; gt;
gt; gt;

How about doing it directly on the times

=AVERAGE(IF(((A2:A128lt;gt;quot;quot;)*(C2:C128lt;gt;quot;quot;)),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)

quot;LauraRosequot; gt; wrote in message
...
gt; the lowdown:
gt;
gt; column A and C are start and stop times. column b is the difference,
gt; calculating minutes to a result. If there is is only one value in column A
or
gt; C I get the #value! error in column B.
gt;
gt; In order to calculate a correct average resolution time I need to those
gt; #value! errors to equal zero so they are disregarded by my average
gt; calculation.
gt;
gt; Here is my formula:
gt; =SUM(G2:G128)/COUNTIF(G2:G128,quot;lt;gt;0quot;)
gt;
gt; Thanks for your help!
gt;
gt;