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