# Average with 3 conditions

#### nelsok

##### Board Regular
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 can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
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))

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

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

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

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

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

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)))}

O2O Statuses.xls
BCDEFGHIJ
35061E1579-05F6-4BAF-9130-D2BA07AE2B8BQCPass11-Apr-200617:47:45 LookupnumberAverage
62063E0AA4-4E41-4034-AC55-6E3976760F7FNotStarted16-Jun-2006143:31:45 InProgress3#DIV/0!
96063E0AA4-4E41-4034-AC55-6E3976760F7FCompleted23-Jun-2006  Completed6#DIV/0!
11207BC3D3D-98C7-4FCD-9DB1-9F58D997AE96NotStarted14-Jul-2006  PendingCSGInfo9#DIV/0!
13207BFDE3E-648E-4FD2-81C1-5DE9E007EE20NotStarted21-Jul-2006
Sheet2

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.

Replies
2
Views
268
Replies
9
Views
248
Replies
5
Views
443
Replies
3
Views
478
Replies
5
Views
1K

1,221,383
Messages
6,159,539
Members
451,571
Latest member
Qwissy

### 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.

### Which adblocker are you using?

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

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