IF(AND) in an array formula
 

I think I'm having problems using IF(AND in an array formula. I've put
together the following formula, which I've gotten help with already from this
discussion group:

{=INDEX(Country;MEDIAN(IF(Owner<"";IF(Category<=9 ;MATCH(Country;Country;0)))))}

With my three named ranges, Country, Owner, and Category, I check to see the
following: For all instances where the owner cell isn't blank, and the
category value is less than or equal to 9, I want to see what the most
frequent Country string is - if there are more German owners than Spanish,
for example.

This works fine, and I'm pleased with it. The problem comes in when I want
to include all Categories between two numbers. What I assumed would work is
the following:

{=INDEX(Country;MEDIAN(IF(Owner<"";IF(AND(Categor y=3;Category<=9);MATCH(Country;Country;0)))))}

The result I get here is #NUM. As far as I can tell, the only thing I'm
doing is substituting the condition that Category is less than a number, with
the condition that Category is between two numbers, which to me seems like a
perfectly reasonable thing to look for in an array... All help is appreciated
as always.

IF(AND) in an array formula
 

=INDEX(Country;MEDIAN(IF(Owner<"";IF(Category=3; IF(Category<=9;MATCH(Country;Country;0))))))

--
__________________________________
HTH

Bob

"Babymech" wrote in message
...
I think I'm having problems using IF(AND in an array formula. I've put
together the following formula, which I've gotten help with already from
this
discussion group:

{=INDEX(Country;MEDIAN(IF(Owner<"";IF(Category<=9 ;MATCH(Country;Country;0)))))}

With my three named ranges, Country, Owner, and Category, I check to see
the
following: For all instances where the owner cell isn't blank, and the
category value is less than or equal to 9, I want to see what the most
frequent Country string is - if there are more German owners than Spanish,
for example.

This works fine, and I'm pleased with it. The problem comes in when I want
to include all Categories between two numbers. What I assumed would work
is
the following:

{=INDEX(Country;MEDIAN(IF(Owner<"";IF(AND(Categor y=3;Category<=9);MATCH(Country;Country;0)))))}

The result I get here is #NUM. As far as I can tell, the only thing I'm
doing is substituting the condition that Category is less than a number,
with
the condition that Category is between two numbers, which to me seems like
a
perfectly reasonable thing to look for in an array... All help is
appreciated
as always.

IF(AND) in an array formula
 

Perfect, thanks. I didn't even think of piling two IFs on each other, even
though I'd already done it with Owner. Now it works fine.

"Bob Phillips" wrote:

=INDEX(Country;MEDIAN(IF(Owner<"";IF(Category=3; IF(Category<=9;MATCH(Country;Country;0))))))

--
__________________________________
HTH

Bob

"Babymech" wrote in message
...
I think I'm having problems using IF(AND in an array formula. I've put
together the following formula, which I've gotten help with already from
this
discussion group:

{=INDEX(Country;MEDIAN(IF(Owner<"";IF(Category<=9 ;MATCH(Country;Country;0)))))}

With my three named ranges, Country, Owner, and Category, I check to see
the
following: For all instances where the owner cell isn't blank, and the
category value is less than or equal to 9, I want to see what the most
frequent Country string is - if there are more German owners than Spanish,
for example.

This works fine, and I'm pleased with it. The problem comes in when I want
to include all Categories between two numbers. What I assumed would work
is
the following:

{=INDEX(Country;MEDIAN(IF(Owner<"";IF(AND(Categor y=3;Category<=9);MATCH(Country;Country;0)))))}

The result I get here is #NUM. As far as I can tell, the only thing I'm
doing is substituting the condition that Category is less than a number,
with
the condition that Category is between two numbers, which to me seems like
a
perfectly reasonable thing to look for in an array... All help is
appreciated
as always.

IF(AND) in an array formula
 

You can AND it by using *

=INDEX(Country;MEDIAN(IF(Owner<"";IF((Category=3 )*(Category<=9);MATCH(Country;Country;0)))))

or even go the whole hog with


=INDEX(Country;MEDIAN(IF((Owner<"")*(Category=3) *(Category<=9);MATCH(Country;Country;0))))



--
__________________________________
HTH

Bob

"Babymech" wrote in message
...
Perfect, thanks. I didn't even think of piling two IFs on each other, even
though I'd already done it with Owner. Now it works fine.

"Bob Phillips" wrote:

=INDEX(Country;MEDIAN(IF(Owner<"";IF(Category=3; IF(Category<=9;MATCH(Country;Country;0))))))

--
__________________________________
HTH

Bob

"Babymech" wrote in message
...
I think I'm having problems using IF(AND in an array formula. I've put
together the following formula, which I've gotten help with already
from
this
discussion group:

{=INDEX(Country;MEDIAN(IF(Owner<"";IF(Category<=9 ;MATCH(Country;Country;0)))))}

With my three named ranges, Country, Owner, and Category, I check to
see
the
following: For all instances where the owner cell isn't blank, and the
category value is less than or equal to 9, I want to see what the most
frequent Country string is - if there are more German owners than
Spanish,
for example.

This works fine, and I'm pleased with it. The problem comes in when I
want
to include all Categories between two numbers. What I assumed would
work
is
the following:

{=INDEX(Country;MEDIAN(IF(Owner<"";IF(AND(Categor y=3;Category<=9);MATCH(Country;Country;0)))))}

The result I get here is #NUM. As far as I can tell, the only thing I'm
doing is substituting the condition that Category is less than a
number,
with
the condition that Category is between two numbers, which to me seems
like
a
perfectly reasonable thing to look for in an array... All help is
appreciated
as always.