« Getting Text From the Windows Clipboard
Weekend Picks »

Logical operations in array formulas

4th December 2004, 06:31 am by Frank Kabel

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

Category: Worksheet Functions  |  Comment (RSS)  |  Trackback

6 Comments

  1. Rita:

    Very educational! If I had kids, I would send them to Excel classes with you.

    11 December 2004, 5:44 am

  2. Frank Brutsaert:

    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.

    13 December 2004, 1:35 pm

  3. Juan Pablo González:

    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…

    13 December 2004, 4:25 pm

  4. Frank Kabel:

    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

    13 December 2004, 6:54 pm

  5. Maria:

    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

    16 April 2008, 11:17 pm

  6. Doug Jenkins:

    =SUM(MIN(A1:C1),MIN(A2:C2),MIN(A3:C3))

    Doesn’t need to be entered as an array formula.

    18 April 2008, 8:19 pm

Certain comments are subject to moderation and may not appear immediately. You can use HTML tags in your comment. If you include a greater-than or less-than sign or anything else that could be interpreted as HTML, your comment won't look nice. You need to escape those characters. To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].

Leave a comment