Archive of Mr Excel Message Board


Back to Forms in Excel VBA archive index
Back to archive home

Averaging quantities of time

Posted by Grace on March 27, 2001 7:26 AM
Our office wants to monitor phone usage. Our system prints out information at the end of the day, and I have been trying to modify an excel spreadsheet into which the data is entered and analyzed. My difficulty is working with quantities of time.

The previous assistant had been entering time incorrectly as decimal amounts, which was disastrous when it came to summing time spent on the phone and averaging time per call.

I have switched to inputing the times using the colon rather than a decimal point, and this has only caused a different set of problems. Apparently, "time" in Excel mean only time on a clock, and not quantities of time. If you add 11 hours to 9 hours, I need the computer to say 20 hours, not 8:00.

There is a secondary problem where the average function is including empty cells in calculations. How can I get it to average only quantities which have been entered and ignore empty cells?


Re: Averaging quantities of time

Posted by Mark W. on March 27, 2001 7:33 AM
Grace, use the custom format:

[h]:mm

I'm having trouble believing that AVERAGE() is
considering empty cells since it's help topic
explicitly states:

"If an array or reference argument contains text,
logical values, or empty cells, those values are
ignored; however, cells with the value zero are
included."


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.