Having trouble with Average Formula

[Robert Wyatt]

I'm having trouble getting my average formula to work could some one please help me figure out what I'm doing wrong?

Sleep Record.xlsm
	B	C	D	E
1	Average Hours Slept Record
2
3				2 hrs 13 min
4				Total Average Hours Slept
5
6	Date	Bedtime	Sleep End Time	Hours Slept
7	Monday April 10, 2023	06:31 PM	02:48 AM	7 hrs 26 min
8	Tuesday April 11, 2023	08:30 AM	10:00 PM	#################
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
Sheet1

Cell Formulas
Range	Formula
E3	E3	=IF(D7="","",AVERAGE(E7:E40))
E7:E40	E7	=IF(D7="","",AVERAGE(IF(D7<>C7,C7-D7)+IF(C7<>D7,C7-D7)))

 

[nemmi69]

you are averaging empty cells as well. Try using =AVERAGEIF(E7:E40,"<>0")

 

[Robert Wyatt]

That works for the Total Averager part, but not for E8. it's the E7:E40 I'm having trouble with. it shows alot of ####

 

[nemmi69]

I suspect its because it goes from AM to PM. Might need someone else to help on that.

 

[Fluff]

How about

=IF(D7="","",IF(C7>D7,D7-C7+1,D7-C7))

 

[Robert Wyatt]

That works just fine thanks for the help. I have been working for the last week on trying to get it to work.

 

[Fluff]

Glad we could help & thanks for the feedback.