formula result #value! needs to equal zero for average calcula
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
"Ron Coderre" wrote:
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
"LauraRose" wrote:
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!
|