Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

(I originally asked this on stackoverflow. However, Excel formulas definitely straddle the line between "programming" and, um, "superusing", so I believe this cross-post is appropriate.)

Many of Excel's built-in functions can take array arguments. Sometimes the result is documented in the help and sometimes not. So:

=IF({1,0,1}, 42, 99)

will return {42, 99, 42}. The help for 'IF' covers array arguments. But:

=INDEX({2,3,5,7,11}, {2,4})

will return {3, 7}. This is intuitive, but I can't find a Microsoft source that documents it. And:

=INDEX({1,2,3,4;5,6,7,8;9,10,11,12;13,14,15,16}, {1,3},{2,4})

returns {2, 12}, which is not intuitive at all.

Is there any source that covers these less-common array usages? It seems like there has to be one, but I'm not finding it in a web search because it requires using "Excel", "function", and "array"...

(Also, can someone tag this question with 'array-formula' since I lack the power to create tags?)

I'm comfortable with using array formulas and use them all the time, but I hate having to figure out what will happen by trial and error. This particular question arose when I was doing the (simplified equivalent of):

=IF(ISNA(udf()), {1,2,3,4}, {5,6,7,8})

where 'udf' was returning, say, {1,2,3}, and the formula was evaluating to {5,6,7,#N/A}. This surprised me, even though I can figure out what is going on and work around it. It just bugs me that I can't find an authoritative source that lays it all out.

Link to question on stackoverflow (there are several answers and some comments, but nothing that satisfies the question right now) here: http://stackoverflow.com/questions/3998632/is-there-any-documentation-of-the-behavior-of-built-in-excel-functions-called-wit

EDIT: I made a large edit in the stackoverflow version of this question, and incorporated part of Neal's answer there. The addition is very much on the "programming" side of Excel formulas, though, so I'm not going to repeat it here.

share|improve this question

2 Answers 2

I don't know of anything useful to go with what you are doing either: I can't remember seeing an example of using array formulae (at least with an array where one would expect to see a single thing. You will see arrays used in examples of the INDEX function).

I think that it is relatively simple if you think of what would happen if you expanded the array (Is there a better terminology for this. Please edit if you can think of one). Where you use an array argument where the function would expect a normal argument, the result is an array with the same number of elements as the array argument, each element of the resulting array being what the result would be with the single argument.

So in your first example

=IF({1,0,1}, 42, 99)

you get an array

{ IF(1, 42, 99), IF(0, 42, 99), IF(1, 42, 99)}

which comes out as

{ 42, 99, 42 }

In your last example

=IF(ISNA({1,2,3}), {1,2,3,4}, {5,6,7,8})

the result is

{ IF(ISNA(1),1,5) , IF(ISNA(2),2,6) , IF(ISNA(3),3,7) ,IF(ISNA(),4,8) }

The first three ISNAs return false and the fourth fails, so the result is what you got. I think the only confusing thing here could be where the error value is.

I thought at first that a result of an error would be more logical than an array of three proper elememts and an element that is an error , but, thinking about it, you can enter an array formula into a worksheet range to match the number of elements in the array. If you think of doing this, the error element makes complete sense and may help you find the error.

share|improve this answer
    
That's a helpful way of looking at it. And I think "expanded" is the right term. See this answer (from Charles Williams) to the Stackoverflow version of this question: stackoverflow.com/questions/3998632/… –  jtolle Oct 25 '10 at 18:16
    
This answer was very helpful to me, so thanks again. I substantially modified my stackoverflow question and borrowed some of your answer in case you're interested. –  jtolle Oct 26 '10 at 0:39
    
I think what is happeneing with the IF example is that ISNA({1,2,3}) evaluates to {FALSE, FALSE, FALSE}, and then to evaluate the IF, Excel expands that to {FALSE, FALSE, FALSE, #N/A}, and then IF(#N/A, 4, 8) evaluates to #N/A. –  jtolle Oct 26 '10 at 0:50
up vote 0 down vote accepted

Charles Williams posted a link to his Decision Models website that has the information I was looking for in his answer on stackoverflow:

http://stackoverflow.com/questions/3998632/is-there-any-documentation-of-the-behavior-of-built-in-excel-functions-called-wit/4003413#4003413

The link itself is:

http://www.decisionmodels.com/optspeedj.htm

(The whole website is an excellent Excel resource, and his FastExcel product is very good, BTW.)

share|improve this answer
    
But the answer to the fundamental question "Is there a list of Excel functions which do (or do not) accept array arguments?" remains a mystery. –  Carl Witthoft Sep 26 '13 at 12:27

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.