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

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
{=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)))}
 
Upvote 0
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
 
Upvote 0
{=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.
 
Upvote 0
{=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.
 
Upvote 0
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)))}
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,184
Members
448,554
Latest member
Gleisner2

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
Back
Top