Average with 3 conditions

[nelsok]

{=AVERAGE(IF(AND($B$2:$B$857=1,E2:E857<DATE(2006,3,1),E2:E857>=DATE(2006,2,1)),$F$2:$F$857))}

I am trying to average cells f2:f857
if the correspoding cells in b2:b857 = 1
AND
E2:E857 is less than 03/01/2006
AND
E2:E857 is greater than or equal to 02/01/2006


b:b contains numbers 1 through 7
e:e contains dates
f:f contains times



any idea what is wrong with my formula and how to fix it?

 

[Brian from Maui]

Try with CSE,

=AVERAGE(IF(($B$2:$B$857=1)*(E2:E857 < DATE(2006,3,1))*(E2:E857>=DATE(2006,2,1))$F$2:$F$857))

 

[Aladin Akyurek]

{=AVERAGE(IF($B$2:$B$857=1,IF($E$2:$E$857>=DATE(2006,2,1),IF($E$2:$E$857 < DATE(2006,3,1),$F$2:$F$857))))}

Also, faster if X2 houses 1-Feb-2006 and you invoke:

{=AVERAGE(IF($B$2:$B$857=1,IF($E$2:$E$857-DAY($E$2:$E$857)+1=X2,$F$2:$F$857)))}

 

[nelsok]

Try with CSE,

=AVERAGE(IF(($B$2:$B$857=1)*(E2:E857 < DATE(2006,3,1))*(E2:E857>=DATE(2006,2,1))$F$2:$F$857))

thanks....

missed one comma though... right before $F$2:$F$857

 

[nelsok]

{=AVERAGE(IF($B$2:$B$857=1,IF($E$2:$E$857>=DATE(2006,2,1),IF($E$2:$E$857 < DATE(2006,3,1),$F$2:$F$857))))}

Also, faster if X2 houses 1-Feb-2006 and you invoke:

{=AVERAGE(IF($B$2:$B$857=1,IF($E$2:$E$857-DAY($E$2:$E$857)+1=X2,$F$2:$F$857)))}

Im getting divide by zero when using the second code. X2 houses 1-Feb-2006 in that format.

 

[Aladin Akyurek]

{=AVERAGE(IF($B$2:$B$857=1,IF($E$2:$E$857>=DATE(2006,2,1),IF($E$2:$E$857< DATE(2006,3,1),$F$2:$F$857))))}

Also, faster if X2 houses 1-Feb-2006 and you invoke:

{=AVERAGE(IF($B$2:$B$857=1,IF($E$2:$E$857-DAY($E$2:$E$857)+1=X2,$F$2:$F$857)))}

Im getting divide by zero when using the second code. X2 houses 1-Feb-2006 in that format.

Book5
	B	C	D	E	F	G	H	I
2	1			12-Feb-06	30		1-Feb-06	35
3	1			20-Feb-06	40
4	2			20-Feb-06	50
5	1			22-Feb-06	35
6	4			3-Mar-06	45
7	2			12-Mar-06	36
8	1			17-Mar-06	60
Sheet1

I2:

=AVERAGE(IF($B$2:$B$857=1,IF($E$2:$E$857-DAY($E$2:$E$857)+1=H2,$F$2:$F$857)))

to be confirmed with control+shift+enter.

You would get a #DIV/0! when there is no 1 in column B and/or no february 2006 dates in column E.

 

[nelsok]

would affect the answer if I have date / time in cells e:e?

 

[Aladin Akyurek]

would affect the answer if I have date / time in cells e:e?

I suppose it would... If you want to disregard the time bit:

{=AVERAGE(IF($B$2:$B$857=1,IF(INT($E$2:$E$857)-DAY(INT($E$2:$E$857))+1=H2,$F$2:$F$857)))}

 

[nelsok]

O2O Statuses.xls
	B	C	D	E	F	G	H	I	J
2	4	061E1579-05F6-4BAF-9130-D2BA07AE2B8B	QCReady	11-Apr-2006	0:35:31		6/1/0612:00AM	through	3/1/2006
3	5	061E1579-05F6-4BAF-9130-D2BA07AE2B8B	QCPass	11-Apr-2006	17:47:45			Lookupnumber	Average
4	6	061E1579-05F6-4BAF-9130-D2BA07AE2B8B	Completed	12-Apr-2006			SpecQCReady	1	#DIV/0!
5	1	063E0AA4-4E41-4034-AC55-6E3976760F7F	SpecQCReady	16-Jun-2006	0:27:11		NotStarted	2	#DIV/0!
6	2	063E0AA4-4E41-4034-AC55-6E3976760F7F	NotStarted	16-Jun-2006	143:31:45		InProgress	3	#DIV/0!
7	3	063E0AA4-4E41-4034-AC55-6E3976760F7F	InProgress	22-Jun-2006	3:08:14		QCReady	4	#DIV/0!
8	4	063E0AA4-4E41-4034-AC55-6E3976760F7F	QCReady	22-Jun-2006			QCPass	5	#DIV/0!
9	6	063E0AA4-4E41-4034-AC55-6E3976760F7F	Completed	23-Jun-2006			Completed	6	#DIV/0!
10	1	07BC3D3D-98C7-4FCD-9DB1-9F58D997AE96	SpecQCReady	14-Jun-2006	720:31:30		OnHold	7	#DIV/0!
11	2	07BC3D3D-98C7-4FCD-9DB1-9F58D997AE96	NotStarted	14-Jul-2006			PendingCSGInfo	9	#DIV/0!
12	1	07BFDE3E-648E-4FD2-81C1-5DE9E007EE20	SpecQCReady	21-Jul-2006	0:18:32
13	2	07BFDE3E-648E-4FD2-81C1-5DE9E007EE20	NotStarted	21-Jul-2006
Sheet2

 

[nelsok]

Only problem with getting rid of the hours is that they are significant to the stats I am creating

Never mind the hours that are significant are in the cells i am averaging.


thanks for the help.