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:

**AND**statement:- Normal usage: AND(condition_1,condition_2)
- Within an array formula: (condition_1) * (condition_2)

**OR**statement:- Normal usage: OR(condition_1,condition_2)
- Within an array formula: ((condition_1) + (condition_2)>0)

**NOT**statement:- Normal usage: NOT(condition_1)
- Within an array formula: (1-condition_1)

**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)

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

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

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.

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…

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

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

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

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

Welldone! Thanks!

Cool workaround… Tnx!

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!

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

XOR is ((condition_1) + (condition_2) = 1)

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.