how do you average time

[nielson]

Hi ,

I need to find the average time it takes students to take exams . I use the following formula =text(end time - start time, "h:mm"). I am able to calculate the amount of time it takes a student to take the exam. Now I need a formula to calculate the average time students take to complete a test. I have over 80 times i need to average. Whenever i try a formula I keep getting 0. Please help

Nielson

 

[Zack Barresse]

welcome to the board!

try not formatting your nuber as text.

if you'd like, post a little example, with Colo's HTML Maker (bottom of page is link).

edit: btw, what does your time look like when you enter it in?

 

[Aladin Akyurek]

=EndTime-StartTime

would suffice to compute the lapsed time.

And, you can simply use:

=AVERAGE(A2:A10)

where A2:A10 houses elapsed times wrt to some test.

 

[nielson]

I changed my formula to =end time - Start time. That worked and gave me each individual time. However when i tried the average my result was #/value!. Any suggestions

 

[Zack Barresse]

you probably still have text in your range.

if your primary formulas contain text, your secondary *average* formula will result in that error.

check and make sure your numbers are formatted as numbers and not text.

HTH

 

[Brian from Maui]

I changed my formula to =end time - Start time. That worked and gave me each individual time. However when i tried the average my result was #/value!. Any suggestions

How are your cells formatted?

Book1
	A	B	C	D
1	5:00	6:00	1:00
2	4:15	7:32	3:17
3	10:07	13:42	3:35
4	12:16	20:55	8:39
5			4:07
Sheet1

From the Help files,

Entering text when the formula requires a number or a logical value, such as TRUE or FALSE. Microsoft Excel cannot translate the text into the correct data type

 

[nielson]

my time look like the following when i enter it

6:00pm (end time) - 3:30pm(start time) = 2:30 (time spent on exam)
Could my problem be that i have time spent on exam formatted as time?

 

[Zack Barresse]

if you custom format as "h:mm AM/PM", your data would look like Brian's and should work just fine.

 

[Brian from Maui]

I changed my formula to =end time - Start time. That worked and gave me each individual time. However when i tried the average my result was #/value!. Any suggestions

Try spacing the am and pm from the time i.e 6:00 PM, not 6:00PM

 

[excelproz]

hey that spacing works wonderfully thanks!