Using Average Function within an ARRAY formula

[ebilbrough]

I'm running Excel2010 and have the following array formula:

=AVERAGE(IF(NW100Database!$R$5:$R$173>="1/1/07"+0,
IF(NW100Database!$R$5:$R$173<"08/01/2011"+0,
NW100Database!$T$5:$T$173)))

Which works fine. I want to also create a formula that ignores any value in COL R that does not equal TODAY. I've added a line so the formula no looks like this:

=AVERAGE(IF(NW100Database!$R$5:$R$173>="1/1/07"+0,
IF(NW100Database!$R$5:$R$173<"08/01/2011"+0,
NW100Database!$R$5:$R$173<> TODAY(),
NW100Database!$T$5:$T$173)))

This results in a #DIV/0! error.

Any help resolving this is greatly appreciated. Thanks.

 

[ebilbrough]

I meant to say that ignores any value in COL R that =TODAY.

 

[T. Valko]

I'm running Excel2010 and have the following array formula:

=AVERAGE(IF(NW100Database!$R$5:$R$173>="1/1/07"+0,
IF(NW100Database!$R$5:$R$173<"08/01/2011"+0,
NW100Database!$T$5:$T$173)))

Which works fine. I want to also create a formula that ignores any value in COL R that does not equal TODAY. I've added a line so the formula no looks like this:

=AVERAGE(IF(NW100Database!$R$5:$R$173>="1/1/07"+0,
IF(NW100Database!$R$5:$R$173<"08/01/2011"+0,
NW100Database!$R$5:$R$173<> TODAY(),
NW100Database!$T$5:$T$173)))

This results in a #DIV/0! error.

Any help resolving this is greatly appreciated. Thanks.

I'd use cells to hold the date criteria:

• V1 = 1/1/2007
• W1 = 8/1/2011

Then, array entered**:

=AVERAGE(IF((NW100Database!$R$5:$R$173>=V1)*(NW100Database!$R$5:$R$173 < W1)*(NW100Database!$R$5:$R$173<>TODAY()),NW100Database!$T$5:$T$173))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[ebilbrough]

Thank you T.Valko.

That worked although I'm still unsure as to why by adding the single line

NW100Database!$R$5:$R$173<> TODAY()

that the formula failed.

 

[T. Valko]

Thank you T.Valko.

That worked although I'm still unsure as to why by adding the single line

NW100Database!$R$5:$R$173<> TODAY()

that the formula failed.

You're welcome. Thanks for the feedback!