Conditional w/ arrays
 

I am trying to get the average of several numbers using arrays. I want
average only if the date with the values are associated with a date range.

{=AVERAGE(IF(AND($E$2:$E$6<I6,$E$2:$E$6<I5),$D$2:$ D$6,""))}

Conditional w/ arrays
 

I've never seen AND and OR work in an array formula. You can get around this
by using multiplication for AND and addition for OR:
=AVERAGE(IF(($E$2:$E$6<I6)*($E$2:$E$6<I5),$D$2:$D$ 6,""))

although I think you could write the formula as
=AVERAGE(IF($E$2:$E$6<MIN(I6,I5),$D$2:$D$6,""))


"PAL" wrote:

I am trying to get the average of several numbers using arrays. I want
average only if the date with the values are associated with a date range.

{=AVERAGE(IF(AND($E$2:$E$6<I6,$E$2:$E$6<I5),$D$2:$ D$6,""))}

Conditional w/ arrays
 

Try this. (array entered)

I assume the dates to average are inclusive of the dates in I5:I6.

=AVERAGE(IF((E2:E6=I5)*(E2:E6<=I6),D2:D6))

--
Biff
Microsoft Excel MVP


"PAL" wrote in message
...
I am trying to get the average of several numbers using arrays. I want
average only if the date with the values are associated with a date range.

{=AVERAGE(IF(AND($E$2:$E$6<I6,$E$2:$E$6<I5),$D$2:$ D$6,""))}