Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array formula | Excel Worksheet Functions | |||
How to set up this array formula? | Excel Discussion (Misc queries) | |||
Array formula | Excel Discussion (Misc queries) | |||
meaning of : IF(Switch; Average(array A, array B); array A) | Excel Worksheet Functions | |||
Array Formula - using LEFT("text",4) in formula | Excel Worksheet Functions |