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!

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.

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.

Try this....

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.

I copy the information from an Excel file, which comes from a database.

Ok, so I formatted the cells as a number. I then pasted the information in. Then is switched the formatting BACK to general. I then changed it to number format again. Still did not calculate the time. Thanks for all the help also!

Then I formmated the Excel from which I pull the information to numbers format. It pastes into the Report as numbers. Still not averaging.

Are the numbers to the left or the right side of the cells?

If they're to the left they're not actually numbers, if to the right then there is no reason why they are not averaging. They do on the version I'm looking at.

Yes, they are to the left. Could it be that it is formatted as TXT in the database? I beleive it was setup that way, as TXT. Im trying to confirm that now. And sorry, when I right click to format, Excel tells me that they are formatted as numbers.

If you could change it in the database to number format rather than text it could well solve your problem. I don't know too much about the inner workings of databases so couldn't guarantee it wasn't set up that way for a specific reason and therefore cannot be changed.

Failing that, you could record a simple macro to change the cells to number format that could be run at the click of a button each time...

On 12/06/2012 6:51 AM, JPP wrote:
I copy the information from an Excel file, which comes from a database.


+-------------------------------------------------------------------+
+-------------------------------------------------------------------+



Hi

I am a little confused as nowhere in the file you have posted does it
have any columns that show any time-stamps.

There are 2 date columns only, so I am curious how you are extracting
time for averaging.

Also, just an observation on your layout, I would consider removing the
merged cells as it could contribute to the math problem.

Cheers
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.