Conditional w/ arrays

P

PAL

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,""))}
 
J

JMB

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,""))
 
T

T. Valko

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