Logical operations in array formulas

Deutsche Version/German version
Array formulas (committed with CTRL+SHIFT+ENTER) have one restriction: You canít use Excel’s logical operations AND, OR, etc. within an array formula. So for example the following formula to conditional evaluate the minimum value of column C is not a valid array formula:

=MIN(IF(AND(A1:A10=”value1″,B1:B10=”value2″),C1:C10))

But you can replace these logical operations in array formulas. Use the following mathematical approaches to mimic the logical functions:

  1. AND statement:
    • Normal usage: AND(condition_1,condition_2)
    • Within an array formula: (condition_1) * (condition_2)
  2. OR statement:
    • Normal usage: OR(condition_1,condition_2)
    • Within an array formula: ((condition_1) + (condition_2)>0)
  3. NOT statement:
    • Normal usage: NOT(condition_1)
    • Within an array formula: (1-condition_1)
  4. NAND statement: This is the negative AND statement. It returns TRUE if neither of the conditions or only one condition are TRUE:
    • Normal usage: NOT(AND(condition_1,condition_2))
    • Within an array formula: ((condition_1) + (condition_2)<>2)
  5. XOR statement: Returns TRUE if only one of the conditions is met:
    • Normal usage: N/A
    • Within an array formula: MOD((condition_1) + (condition_2),2)=1

So using the first formula as example you can use the above approach as follows:

=MIN(IF((A1:A10=”value1″)*(B1:B10=”value2″),C1:C10))

to create a valid array formula.

Pages: 1 2

Posted in Uncategorized

18 thoughts on “Logical operations in array formulas

  1. Frank, in fact your way of using array formulas also mimics the database functions, which shows what a powerful technique it is. Without the second dimension, it would look like =MIN(IF((A1=”value1?)*(B1=”value2?),C1)); so leaving away rows 2 to 10.

    Having in mind that VBA arrays can have more than two dimensions, I wanted to ask you if you ever applied your conditional array technique in VBA.

  2. Frank (B),

    I have. I think its a lot faster than doing a loop. Just put the formula inside an Evaluate() and you’re done, *but* you have to be careful not to exceed the 255 character limit…

  3. Hi Frank (B)
    thanks for your comment. I have done the same as Juan described in his post. Only problem (as stated) is the 255 characters limit :-(
    Frank

  4. Hello,

    I have an array formula question involving “sum” and “minimum”. I would like to sum the minimums of two columns. For example:

    A B min(A,B)
    1: 1 2 1
    2: 5 4 4
    3: 4 3 3

    I would like to sum the minimum of each row, i.e. 1+4+3 to get 8, however when I use the array formula (SUM(MIN(A1:A3,B1:B3)) I get 1. Is there a way this can be done in excel without using SUM(IF(A45:A47

  5. Thanks for this tip Frank! It’s been years since you wrote this post but it still rings true using Excel 2010. It’s disappointing that this hasn’t been mentioned in the official documentation of the AND function. Thanks for catching this up and suggesting a workaround!

  6. Thank you! You have just saved my day. I don’t understand why Excel doesn’t tell you about this when you wnter a logical operator inside an array formula !!!

  7. Thank you, you saved my day.
    I can only agree with the previous posts: Why don’t they tell you? And, even more sensible: Why does MS not fix it?

    BTW: I also found out that a simple operation ‘A1:A3 > “””‘ doesn’t work like it does in a normal cell calculation. Hyppily, ISBLANK came to my rescue.


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.