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!

When you say you tried "paste special" do you mean "paste special / values"?
If so then it will work on the proviso the cells you're pasting into are formatted the same as the others that do work.

Give that a try, and if still no luck, post an example workbook (without confidential information) on here and someone will happily help out.

S.

Spencer,

Whenever I attempt to do a paste special with values, then Excel doesnt calculate ANYTHING. And there is ALOT of calculating going on. I would rather double click every cell, then have nothing calculate. I don't get it, the average call time is the only one that doesn't work, until I double click on the cell containing the time of the call.

I am trying to upload a copy of this, but unfortunately, it is over the size limit. It is a very big project. Yikes.

Hi,

I've PM'd you an email address. If you want to ping your spreadsheet there I can take a look at it, then come back here with the answer so others can benefit.

S.

I got it. I just took out some of the stuff I didn't need help with. Thanks.

The numbers are formatted as text, which is why the average formula is not picking them up.

As far as I see it, if you fix the root problem all will work well into the future.
When you said you copy and paste this data in, where do you copy it from?
If from another workbook then fix the formatting in that/those one/ones and you should be OK.

Let me know if you need more help.

Try this....

On 11/06/2012 11:19 PM, 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!




Hi

Make sure the cells you are pasting to are formatted as [h]:mm

Then you can PasteSpecial.Value into the cells
HTH
Mick.

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.