Average with 3 conditions

nelsok

Board Regular
Joined
Jan 20, 2006
Messages
166
Code:
{=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?
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Brian from Maui

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,458
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
{=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

Board Regular
Joined
Jan 20, 2006
Messages
166
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

Board Regular
Joined
Jan 20, 2006
Messages
166

ADVERTISEMENT

{=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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
{=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
BCDEFGHI
2112-Feb-06301-Feb-0635
3120-Feb-0640
4220-Feb-0650
5122-Feb-0635
643-Mar-0645
7212-Mar-0636
8117-Mar-0660
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

Board Regular
Joined
Jan 20, 2006
Messages
166

ADVERTISEMENT

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

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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

Board Regular
Joined
Jan 20, 2006
Messages
166
O2O Statuses.xls
BCDEFGHIJ
24061E1579-05F6-4BAF-9130-D2BA07AE2B8BQCReady11-Apr-20060:35:31 6/1/0612:00AMthrough3/1/2006
35061E1579-05F6-4BAF-9130-D2BA07AE2B8BQCPass11-Apr-200617:47:45 LookupnumberAverage
46061E1579-05F6-4BAF-9130-D2BA07AE2B8BCompleted12-Apr-2006  SpecQCReady1#DIV/0!
51063E0AA4-4E41-4034-AC55-6E3976760F7FSpecQCReady16-Jun-20060:27:11 NotStarted2#DIV/0!
62063E0AA4-4E41-4034-AC55-6E3976760F7FNotStarted16-Jun-2006143:31:45 InProgress3#DIV/0!
73063E0AA4-4E41-4034-AC55-6E3976760F7FInProgress22-Jun-20063:08:14 QCReady4#DIV/0!
84063E0AA4-4E41-4034-AC55-6E3976760F7FQCReady22-Jun-2006  QCPass5#DIV/0!
96063E0AA4-4E41-4034-AC55-6E3976760F7FCompleted23-Jun-2006  Completed6#DIV/0!
10107BC3D3D-98C7-4FCD-9DB1-9F58D997AE96SpecQCReady14-Jun-2006720:31:30 OnHold7#DIV/0!
11207BC3D3D-98C7-4FCD-9DB1-9F58D997AE96NotStarted14-Jul-2006  PendingCSGInfo9#DIV/0!
12107BFDE3E-648E-4FD2-81C1-5DE9E007EE20SpecQCReady21-Jul-20060:18:32 
13207BFDE3E-648E-4FD2-81C1-5DE9E007EE20NotStarted21-Jul-2006  
Sheet2
 

nelsok

Board Regular
Joined
Jan 20, 2006
Messages
166
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.
 

Forum statistics

Threads
1,136,307
Messages
5,674,986
Members
419,541
Latest member
freddyboots

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top