IF AND formula help

[Harvey Waxman]

I'm hopeless.

{=AVERAGE(IF(AND($H$2:$H$11000="",$K$2:$K$11000<0),$K$2:$K$11000))}

I'm trying to get the average of column K where column H is a blank and
K is less than 0.

I get a 0 for the result.

 

[JE McGimpsey]

I'm hopeless.

{=AVERAGE(IF(AND($H$2:$H$11000="",$K$2:$K$11000<0),$K$2:$K$11000))}

I'm trying to get the average of column K where column H is a blank and
K is less than 0.

I get a 0 for the result.

Try (array-entered: CMD-RETURN):

=AVERAGE(IF(($H$2:$H$11000="")*($K$2:$K$11000<0),$K$2:$K$11000))

 

[Harvey Waxman]

Try (array-entered: CMD-RETURN):

=AVERAGE(IF(($H$2:$H$11000="")*($K$2:$K$11000<0),$K$2:$K$11000))

Of course. But what I wrote seems to make perfect sense too. It's as
if Excel won't listen if I speak with a different accent :-(

Just how does Excel interpret my formula?

Thanks.

 

[JE McGimpsey]

Of course. But what I wrote seems to make perfect sense too. It's as
if Excel won't listen if I speak with a different accent :-(

Know the feeling - I have a similar problem with AppleScript...

Just how does Excel interpret my formula?

The AND() function returns a scalar, meaning that

AND({TRUE,FALSE,TRUE},{TRUE,TRUE,TRUE})

will always return FALSE, as long as ANY argument evaluates to FALSE.

So, assuming at least one value in H2:H11000<>"" or that at least one
value in K2:K11000>=0, your formula

=AVERAGE(IF(AND($H$2:$H$11000="",$K$2:$K$11000<0),$K$2:$K$11000))

evaluates to

=AVERAGE(IF(FALSE,$K$2:$K$11000))

which will always return 0

 

[Harvey Waxman]

Know the feeling - I have a similar problem with AppleScript...


The AND() function returns a scalar, meaning that

AND({TRUE,FALSE,TRUE},{TRUE,TRUE,TRUE})

will always return FALSE, as long as ANY argument evaluates to FALSE.

So, assuming at least one value in H2:H11000<>"" or that at least one
value in K2:K11000>=0, your formula

=AVERAGE(IF(AND($H$2:$H$11000="",$K$2:$K$11000<0),$K$2:$K$11000))

evaluates to

=AVERAGE(IF(FALSE,$K$2:$K$11000))

which will always return 0

Ouch. Your description of AND sounds to me more like OR. Must be my
accent.

Thanks

 

[JE McGimpsey]

Ouch. Your description of AND sounds to me more like OR. Must be my
accent.

OR()'s similar except that it will return TRUE if *any* argument is TRUE.

Again, AND() returns FALSE unless *all* arguments are TRUE.

 

[Harvey Waxman]

OR()'s similar except that it will return TRUE if *any* argument is TRUE.

Again, AND() returns FALSE unless *all* arguments are TRUE.

I see....until the next time.

Thanks

I'll post another IF challenge under a new subject