Complex if

  • Thread starter Thread starter PAL
  • Start date Start date
P

PAL

This formula returns a "false" but should return a number: As an array.....

=IF(AND($I$2:$I$3536>"",$H$2:$H$3536>"",$H$2:$H$3536>=10/1/2008,$H$2:$H$3536>11/1/2008),AVERAGE($J$2:$J$3536,""))

Columns I and H are dates.

Thanks.
 
It can return FALSE because of the end of the formula:
Change AVERAGE($J$2:$J$3536,"")) to AVERAGE($J$2:$J$3536),"")
 
Hi,

I think there's a few issues with the fromula

Condition 1 & 2
$I$2:$I$3536>"",$H$2:$H$3536>"",

Do you mean
$I$2:$I$3536<>"",$H$2:$H$3536<>"",

Conditions 2 & 3
$H$2:$H$3536>=10/1/2008,$H$2:$H$3536>11/1/2008)
Perhaps you mean
$H$2:$H$3536>=date(2008/10/1)

Excel will interpret 10/1/2008 as 10 divided by 1 divided by 2008 which
equals 0.00498 and not a date

Argument 2 & 3 are contradictory.

anything >=1 Oct 2008 will catch a date > 1 Nov 2008 so what is the point
of the second date argument.

Do you mean >= 1 Oct 2008 < 1 Nov 2009

perhaps you could explain what it is your trying to achieve and someone will
help.

Mike
 
loking at it all again I think you mean this

=AVERAGE(IF(H2:H3536>=DATE(2008,10,1),IF(I2:I3536<>"",IF(I2:I3536<DATE(2008,11,1),J2:J3536,""))))

Mike
 
Back
Top