Averaging time in hh:mm format

[Ryan]

I am trying to average 'call handle time' for a week.

1) I use VLOOKUP to pull the data from a daily report
2) I have applied a format of [h]:mm:ss

The cells look like this -- it's throwing a #DIV/0! error. Any ideas?
D6 E6 F6 G6 H6
00:05:10 00:04:17 00:04:50 00:05:21 00:05:10 #DIV/0!

 

[Niek Otten]

What is your formula?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I am trying to average 'call handle time' for a week.
|
| 1) I use VLOOKUP to pull the data from a daily report
| 2) I have applied a format of [h]:mm:ss
|
| The cells look like this -- it's throwing a #DIV/0! error. Any ideas?
| D6 E6 F6 G6 H6
| 00:05:10 00:04:17 00:04:50 00:05:21 00:05:10 #DIV/0!
|

 

[Dave Peterson]

If you're getting #Div/0 as a result of a formula like:
=average(a1:e1)
then a1:e1 doesn't contain any numbers
(and dates/times are just numbers nicely formatted to excel)

If you still have the =vlookup() formulas in those 5 cells, then I'd go back to
the original data and convert those text times to real times. (They're strings
masquerading as numbers.)

Maybe this will help:
Select the range to fix
edit|Replace
what: : (a colon)
with: : (that same colon)
replace all

If there's nothing else in that cell (like those HTML non-breaking spaces), then
you may see your formula evaluate to a real number.

In general, you may want to use a formula like this:
=if(count(a1:e1)=0,"No Numbers",average(a1:e1))

I am trying to average 'call handle time' for a week.

1) I use VLOOKUP to pull the data from a daily report
2) I have applied a format of [h]:mm:ss

The cells look like this -- it's throwing a #DIV/0! error. Any ideas?
D6 E6 F6 G6 H6
00:05:10 00:04:17 00:04:50 00:05:21 00:05:10 #DIV/0!