Complex if

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

 

[Bob Umlas]

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

 

[Mike H]

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

 

[Mike H]

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

 

[David Biddulph]

A few points:
1 I have a feeling that AND doesn't work with arrays.
2 You are comparing column H with a very small number, 10 divided by 1
(sic) then divided by 2008.
3 You say that I and H are dates. If H is > 11 Oct 08 it is bound to be

= 10 Oct 08. Perhaps you intended the 11/1/08 comparison to be < rather

than > ?
4 You are trying to take the average of a range which includes column J
and the null text string "", but of course the AVERAGE function ignores text
strings, so there is no point in including it in the argument for AVERAGE.
I guess that you may have intended the penultimate parenthesis to be before
the preceding comma, so that the "" is not included in the AVERAGE but as
the alternative outcome of the IF, but I think you probably wanted the
AVERAGE to be the outer function and the IF to govern which rows of column J
were to be included in the average?
5 You are testing for columns I and H being >"", but if H or I is a date,
it is a number, and a number will not be greater than the text string.
.... and there may be other points.

Guessing at what you might have wanted, perhaps:
=AVERAGE(IF(($I$2:$I$3536<>"")*($H$2:$H$3536<>"")*($H$2:$H$3536>=DATE(2008,10,1))*($H$2:$H$3536<DATE(2008,11,1)),$J$2:$J$3536,""))as an array formula--David BiddulphPAL wrote:> 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.