Divide time per script number

[mv8167]

Hi All,

I thought this would be easy.

I have a time of a test execution of 01:015:07 (A1) (HH:MM:SS) with 506 tests(B1). All I want to do is find the average time per test ... so "= A1/B1"

I get 0:00:07 secs, not right.

Do I need to convert my time to secs, then divide, then turn back to full time format?

thx all

 

[jtakw]

Hi,

Looks fine to me?

Book3.xlsx
	A	B	C
1	1:15:07	506	0:00:09
Sheet917

Cell Formulas
Range	Formula
C1	C1	=A1/B1

 

[kapvg]

I have a time of a test execution of 01:015:07 (A1) (HH:MM:SS) with 506 tests(B1). All I want to do is find the average time per test ... so "= A1/B1"

I am assuming there is a small typo in the above and you meant 01:15:07 i.e. 1hr 15min and 7 seconds.

01:15:07 i.e. 1hr 15min and 7 seconds = 4507 seconds and 4507 / 506 = 8.907seconds. Thus the average time per test = 8.907 seconds rounded off to 9 seconds

As you have the 01:15:07 value in a cell formatted as (HH:MM:SS) and divide this cell with 506 the result will be 0:00:09 (i.e. 9 seconds). Please remember that the result is also in the HH:MM:SS format.

Hope this is what you were looking for.

 

[mv8167]

I am assuming there is a small typo in the above and you meant 01:15:07 i.e. 1hr 15min and 7 seconds.

01:15:07 i.e. 1hr 15min and 7 seconds = 4507 seconds and 4507 / 506 = 8.907seconds. Thus the average time per test = 8.907 seconds rounded off to 9 seconds

As you have the 01:15:07 value in a cell formatted as (HH:MM:SS) and divide this cell with 506 the result will be 0:00:09 (i.e. 9 seconds). Please remember that the result is also in the HH:MM:SS format.

Hope this is what you were looking for.

Thx, the number seemed to small. The only remaining issue is that the time is then a clock time with AM/PM, I just need the time length.

 

[mv8167]

Hi,

Looks fine to me?

Book3.xlsx
	A	B	C
1	1:15:07	506	0:00:09
Sheet917

Cell Formulas
Range	Formula
C1	C1	=A1/B1

thank you, I was good, my time just seemed to small

 

[mv8167]

both responses were perfect, thx

 

[jtakw]

You're welcome, thanks for the feedback.

 

[mv8167]

Hi,

Looks fine to me?

Book3.xlsx
	A	B	C
1	1:15:07	506	0:00:09
Sheet917

Cell Formulas
Range	Formula
C1	C1	=A1/B1

I retried just doing what I had before =A1/B2, now I get 0.00. If I u

Hi,

Looks fine to me?

Book3.xlsx
	A	B	C
1	1:15:07	506	0:00:09
Sheet917

Cell Formulas
Range	Formula
C1	C1	=A1/B1

If I A1 -515, B2=01:31:00
se =(HOUR(B2)*3600 + MINUTE(B2)*60 + SECOND(B2))/A1 i get 10.60 (format is Numeric "0.00") if I change to the Time format "13:30:55" I get 14:26:48 ... What am I doing wrong...lol

 

[jtakw]

Is that -515, a negative number?
You can't divide Time with a negative.

Try =B2/ABS(A1)

Also, keep B2 and your Result cell formatted as "Time" without the AM/PM, or use custom format like:
[hh]:mm:ss
[h]:mm:ss