On Mon, 11 Jun 2012 13:19:11 +0000, JPP wrote:


I have a simple AVERAGE function, it averages the call time for all
agents. I am not having troubles with the function itself though. The
problem is when I copy and paste the information into the workbook, the
information shows, but it does not "average" the time. I have to double
click on each cell individually, after I double click on every cell with
a time in it, it then averages the call time. Why does this not work
right after pasting the information in? I have also tried doing a "paste
special" but that did not work either. Thanks in advance!

Your data in the time column, on the sheet that you posted for examination, is TEXT. The average function ignores text, hence the div/0 error.

There is probably something in your import process, from the original database, that is resulting in your time values being interpreted as text strings, rather than as numbers.
I would suggest reviewing your import method, and ensuring that these numbers are imported as numbers.

Alternative methods of dealing with the problem:
Convert the text strings to numbers after import:
Method 1 you are already using -- Edit each cell individually -- but that is tedious
Method 2: Place a 1 in some unused cell.
Select the cell
Edit/Copy
Select 'CCI Info'!L2:L101
Edit/Paste Special Operation: Multiply

Change the Average formula to handle the data as written. e.g: the array formula (entered with <ctrl<shift<enter)

=AVERAGE(IF('CCI Info'!L2:L101="","",--'CCI Info'!L2:L101))

would do that EXCEPT the cell in which you have the formula is a MERGED cell. While merged cells are often convenient, they have many limitations. One of these limitations is that you cannot have an ARRAY formula in a merged cell! So you would have to change the layout of your worksheet to use this solution.