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?
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."
