Way to divide time by a number?

[usmdesigner]

My example is that I have 2038 total reports. I want to find out the average amount of time it took to read each reports based on a total time of 1.08:11:21

It seems like I would need to convert the time to minutes, but haven't been successful in doing that through Excel yet (I'd rather not do it on my own outside of Excel). Is there a formula that can do this based on the columns and numbers I have now without having to break this down?

I tried just a simple =a1/b1 and that didn't work.

[Odracir]

Hey there,

May you please attach a worksheet as an example? It would very much make it easier

Best Regards,
Odracir

[usmdesigner]

Yes I can. Thank you

Trying to figure out how F11 and O11 could be divided to find the average of each report

[shg]

One way:

Row\Col	O	P	Q
9	Audio Duration
10
11	1.08:11:21	32:11:21	P11: =IFERROR(--O11, LEFT(O11, FIND(".", O11)) + MID(O11, FIND(".", O11) + 1, 99))
12	06:08:17	6:08:17
13	21:42:00	21:42:00
14	19:58:16	19:58:16
15	1.20:00:32	44:00:32
16	1.08:45:52	32:45:52
17	20:53:22	20:53:22
18	1.03:35:22	27:35:22
19	06:48:44	6:48:44
20	08:08:21	8:08:21
21	1.10:23:12	34:23:12
22	07:45:50	7:45:50
23	09:45:18	9:45:18
24	12:48:16	12:48:16
25	1.08:36:41	32:36:41
26	08:43:11	8:43:11
27	17:23:41	17:23:41
28	00:19:17	0:19:17
29	21.06:25:54	343:57:33	P29: =SUM(P11:P28)
30	Average	19:06:32	P30: =AVERAGE(P11:P28)

The format of col P is [h]:mm:ss

[usmdesigner]

One way:

Row\Col	O	P	Q
9	Audio Duration
10
11	1.08:11:21	32:11:21	P11: =IFERROR(--O11, LEFT(O11, FIND(".", O11)) + MID(O11, FIND(".", O11) + 1, 99))
12	06:08:17	6:08:17
13	21:42:00	21:42:00
14	19:58:16	19:58:16
15	1.20:00:32	44:00:32
16	1.08:45:52	32:45:52
17	20:53:22	20:53:22
18	1.03:35:22	27:35:22
19	06:48:44	6:48:44
20	08:08:21	8:08:21
21	1.10:23:12	34:23:12
22	07:45:50	7:45:50
23	09:45:18	9:45:18
24	12:48:16	12:48:16
25	1.08:36:41	32:36:41
26	08:43:11	8:43:11
27	17:23:41	17:23:41
28	00:19:17	0:19:17
29	21.06:25:54	343:57:33	P29: =SUM(P11:P28)
30	Average	19:06:32	P30: =AVERAGE(P11:P28)

The format of col P is [h]:mm:ss

Thanks shg, but I'm trying to find the average of column F11 with O11. I'm trying to find how much time it took on average per report, and each row has different values. So each row would need column F (total reports) taken into effect.

[shg]

but I'm trying to find the average of column F11 with O11.

I don't think I know what that means.

If you divide P29 by 39518, the result is 0:00:31 (31 seconds per report).

EDIT: BTW, the total shown (39518) is not correct.

[samba_ravi]

=SUBSTITUTE(O11,".","-Jan-1900 ")/F11
try this formula, if it is showing number change the cell format from general to Time format

[samba_ravi]

the given formula will not work if 1.10:23:12 the color part is 32 & above

[usmdesigner]

=SUBSTITUTE(O11,".","-Jan-1900 ")/F11
try this formula, if it is showing number change the cell format from general to Time format

Perfect!! This worked great. I'll take into account about the day though. I don't see a situation where that will hit 32 and above

Is there somewhere that explains how that formula works?

[usmdesigner]

Perfect!! This worked great. I'll take into account about the day though. I don't see a situation where that will hit 32 and above

Is there somewhere that explains how that formula works?

It looks like my original documentation is off now that I plugged that in. I had deleted other information off the document, that I assume has an effect on your formula. Which is odd, because I just deleted information inside some columns that have nothing to do with column F and O.

Now my numbers look like:
0.000658104
0.000482552
0.000550985
0.000345712

Where they were
00:57
00:42
00:48
00:30

Maybe if I understood the formula, I could fix it?

[usmdesigner]

Here is my original with names taken out

[samba_ravi]

the cell is in number format so change it to time format
as said below
select the cells
format cells (press Ctrl+1)
select number
under Category select time
under type select 37:30:55

or use below formula
=TEXT(SUBSTITUTE(O11,".","-Jan-1900 ")/F11,"[h]:mm:ss")

[usmdesigner]

the cell is in number format so change it to time format
as said below
select the cells
format cells (press Ctrl+1)
select number
under Category select time
under type select 37:30:55

or use below formula
=TEXT(SUBSTITUTE(O11,".","-Jan-1900 ")/F11,"[h]:mm:ss")

Argggg. sorry. got it now. Thanks!