In Excel worksheet formulas there is a certain type of formula that is enclosed in brackets, which enables the return of an array / range rather than a single value.

I will say first that I AM NOT talking about 'array formulas' that are enclosed in { } - that you enter with CTRL+SHIFT+ENTER. I AM NOT talking about setting operator precedence.

For example:

The formula below will return the row number of the top cell in the range. The value 10

Now I know from my own experience how useful this type of syntax is but I am unable to research it on the internet to find other uses since I have no idea what this syntax is called. Each time you look up 'Excel formula brackets' or 'Excel array return' you get countless results about array formulas.

I can find nothing about this very useful formula syntax and would like to explore additional uses and tricks.

Can anyone tell me what this syntax is referred to or direct me to some good resources ?

Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:

Hello,

it's still an array.

Entered like this and array entered in one cell, the formula

=(ROW(A10:A20))

will still return just the first result of the array, i.e. the value 10. If several cells are selected and the formula is entered into the first cell and then array entered, the second cell will show the value 11, the next one 12, etc. The outer round braces can be omitted for that exercise.

The expression (ROW(A10:A20)) only returns an array of the numbers 10 to 20 if the formula it is used in is either array entered with Ctrl-Shift-Enter, or if the formula it is used in is in itself an array formula.

Compare

=sum(ROW(A10:A20)) with simple Enter
=sum(ROW(A10:A20)) with Ctrl-Shift-Enter

and

=sumproduct(ROW(A10:A20)) with simple Enter.

Sumproduct() is inherently an array function and will interpret (ROW(A10:A20)) as an array, even if confirmed with a simple Enter. Sum() however, is not an array function and to make it regard (ROW(A10:A20)) as an array instead of just the first value, it needs to be array entered.

There are many possibilities to exploit this behaviour and help create shorter and more concise formulas.

Also of note is that named formulas, i.e. the formulas entered to define a named range are always array formulas. A named range "MyRange" with the formula

=SUM(ROW(Sheet1!A10:A20))

Entered into a worksheet cell like

=MyRange

this will return the sum of the row numbers. If the range is defined with relative references, as in the example, the result will be different, depending on what row the =MyRange name is called upon. This can be a VERY powerful tool, since very often, helper calculation cells in worksheets can instead be carried out by such range names.

Good resources for learning more about arrays are Chip Pearson's site and Daniel Ferry's ExcelHero site.

Here the 'ROW' function is returning an array of values - rather than just the 1st value. It is not being caused by an array function and this is not entered as an array formula. It is a regular formula. The MAX array function is 'consuming' (or expecting) an array but it is not creating it. It appears the brackets are creating the array.

The '(F13:F19=E13)' also returns an array of values and there is no function here used whatever.

Even the Microsoft help documentation indicates that ROW is not an array function and if passed a range it will only return the row of the 1st cell in that range. To get multiple row values you need to select multiple cells and enter the same formula as an array formula. Using brackets will allow an array of values to be returned without entering as an array formula.

If in fact you look at Chip person's page (see below), on array formulas, it does show that the use of '(' and ')' to enclose an range forces the return of an array / builds the array

Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:

I hear you.

Index() is one of those functions that are inherently array functions. (Lookup() is another one, btw.) Since the Max() function is nested in an Index() function, it is not necessary to array enter the formula. Index() will treat it as an array, no matter what.

And in your Index/Max example, the formula is multiplying one array with another array, which returns an array of results.

They are still all arrays.

This is advanced array functionality and it may take some time to get your head around it. Some Excel functions handle arrays without Ctrl-Shift-Enter. Others don't. Some combinations of nesting non-array functions inside array functions will force the non-array function to return an array. Sometimes that is good, sometimes it is counter productive.

It really depends on the situation.

It is really hard to study the subject academically without a business need. If you have a business need, it will be much easier to come up with search terms that yield meaningful results.

There are a few brilliant experts of array formulas active in the on-line communities. Look out for barryhoudini (or barry houdini), NB_VC (or NBVC), Aladin Akyurek, Chip Pearson. Apart from Chip, though, they don't have their own sites. Try and find their posts and suggestions and see what can be done in specific situations.

0

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

And in your Index/Max example, the formula is multiplying one array with another array, which returns an array of results.

They are still all arrays.

I understand about the arrays. That is more my point - normally that syntax would only return 1 value without the use of an array function. The brackets () appear to force the return of an array or 'override' what the consuming function is expecting (similar to an 'array entered' function but without the CSE entry)

'rorya' really confirms my experience, although it makes it more confusing...

the brackets are irrelevant in that formula.

I have had allot of experience with array formulas, array functions and using this 'bracket syntax' and I agree it is just something you have to get an instinctive understanding on based on experience.

My question still stands: what official description / term is used to describe this ability of the 'range in brackets' syntax to force a different return value ?

It is demonstrated and it's functionality implied on a number of sites (e.g. Chip's) but never directly referred to by name.

To use my earlier example; ROW is not an array function yet will behave like one on some occasions (which appear to use the brackets) without using CSE.

Is this bracket notation simply an artifact of Excel that actually means nothing and can be used or excluded ? If the brackets are not actually required then the only explanations that makes sense is that the formula is evaluated from the outside-in (in terms of formulas) so it looks at the outer formula and says 'what type of value do we expect ?' and then converts the inner formula / range result into that value type regardless of what the function normal expects.

Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:

It's not the brackets.

see =Sum((Row(A10:A20))

The brackets are there, but the formula returns a 10.

It's not the brackets.

It is the context in which the range is interpreted. If the range is passed to a function that natively interprets a range as an array, then an array will be returned. See SumProduct(), Lookup, Index (depending on further details), and some others, and ALL named range formulas.

If it is the context then ask SUM is an array function and expects an array ROW should be forced to return array - and yet it doesn't

Now for the example '=INDEX(MAX((F13:F19=E13)*ROW(F13:F19)),0,0)' the ROW function is being force to return an array by the array function MAX - if it didn't then multiplying it's return by the array would produce different results. This suggests that it is not entirely context but also some other factor.

I can accept the brackets are just there to separate individual terms of the calculation but otherwise I cannot see how a function that is not array can be forced to act like one without CSE

SUM is not an array function per se - it will work equally well with scalar arguments.

the ROW function is being force to return an array by the array function MAX

If that were true, then:
=MAX(5*ROW(F13:F19))
would return 95 not 65 without array entry and
=MAX(ROW(F13:F19))
would return 19 rather than 13. Like SUM, MAX will accept scalar arguments happily.

Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:

Now for the example '=INDEX(MAX((F13:F19=E13)*ROW(F13:F19)),0,0)' the ROW function is being force to return an array by the array function MAX - if it didn't then multiplying it's return by the array would produce different results. This suggests that it is not entirely context but also some other factor.

Your premise is wrong.

>> the ROW function is being force to return an array by the array function MAX

No. Max does not force Row to return an array.

Row() is not an array function and neither is Max(). But the operation of combining Row() and Max() with a multiplication operator will return an array. Index() is a function that handles arrays natively. Therefore, nesting of (Max() * Row()) inside of Index() is a perfectly natural subject for Index to deal with.

Nothing is being forced. Index just does what it does best: deal with arrays.

A poor choice of words on my part. As you say ROW is not an array function but in that context it is returning an array and is therefore 'forced', on it's own ROW will only result 1 value if is passed an array / range.

What I meant is as follows:

Index is an array function and works on arrays. It therefore views its parameters in those terms.

MAX may accept scalar values but that is more an array of 1 item than the function that can only accept 1 item. After all MAX would make no sense with one input as it would always be the same value. That to me makes MAX and array function - it is what it was designed to deal with.

If the outer array functions are internally using a scalar function like ROW then it must mean that either the ROW is being forced to return an array or (more likely) it is being repeated for every item in the passed range in order to produce the array before the two arrays are combined.

So the questions become

If the brackets are only to separate terms in the arguments (because it relies on context) is it then always the case that a scalar function will be run multiple times and all it's responses collected as an array which is then processed ?

Does this happen for every scalar function used in an array context without using CSE to enter an array formula ?

I think perhaps you miss the point about MAX (and SUM)

Max(some_array)
and
Max(value1, value2, value3, value4)

are equally valid even though there are no arrays involved in the latter.

I do not believe that scalar functions calculate any differently whether used in an "array context" or entered with CSE. Having said that, I have no idea how you would prove it either way.

I am fascinated watching this thread, something I have been musing upon for some time.

maybe I can have a crack at that " how you would prove it either way". It looks like this is about scalar formulae iterating or being iterated within themselves, or looping until done; one might say "doing while", thusly:

Looks to me like this formula (drafted in from an old post quite possibly by one of the existing posters above) goes round and round "adding if" until done, within each instance of itself. Seems to me that is the same characteristic as is being presented when a scalar formula is buried inside an array formula; so the scalar formula is merely doing what scalar formulae are designed to do.

How's that?

edit: I tried googling various words to describe this but got nowhere.

when I use the word "iterate" I mean generically, nothing to do with Excel's iteration features.

I think the point I am trying to make (although perhaps badly) is that the idea of 'array functions' is almost irrelevant and confusing since even scalar 'non-array functions' can be 'forced' to return arrays. Through the use of brackets to indicate the 'context' or parameter resolution order you can make scalar functions return arrays.

To give an example (with DATA as a named multi-cell range A1:A10):

... returns '28'. Because 'INDEX' expects and uses an array as a parameter the ROW function has been 'forced' to return / use an array (probably by iterating through the single cells in it's parameter).

... then again you get '28' which has appears to have iterated the scalar version of the ROW formula (as above) before summarizing the result.

In this final example, whereas normally you use CSE to return an array of values into multiple cells it can be used to force the context of parameter values (here the ROW function) to be iterated BEFORE the final / outer scalar function is executed.

From my experience it does appear that the line between array and non-array functions is a very fine one and it is very easy to confuse 'array functions' and 'array formulas'. As I would explain it: 'array formulas' refer to the return of an array from the final / outer function whereas 'array functions' are those that accept arrays as inputs.

Additionally whereas CSE entry is normally explained as only being used for array formulas - to return an array, it can also be used on 'scalar' formulas to change the way the formula is evaluated (array or scalar context) but still return a scalar value (which would then mean they are not array formulas).

So what I am trying to find out is: what is the term (or is there any) that is used to refer to this 'flexible function return' ability. I want to search on the internet and learn what other non-array functions can behave like this by controlling the context (or maybe they all can).

for the record 28 == 55 presumably a typo somewhere.
----------------------------------------------------------------------

Short answer: ". then again you get '28' which has appears to have iterated the scalar version of the ROW formula (as above) before summarizing the result. "

I think the ROW function is presenting a list to be summed. I think SUM is not being presented with a series of results each to add to it's cumulating total. My (extensive) reasoning is below.

Longer answer:

have you an example of that where the evaluated array is more than one dimensional? i.e. where the inner result is not a one dimensional list?

The reason I ask is this:

Call-by-value is not a single evaluation strategy, but rather the family of evaluation strategies in which a function's argument is evaluated before being passed to the function.

but a list (which gets rendered and can be indexed in exactly the same way) is a value.

"a value" means "one" means "scalar", in computer science language: seems to me.

That means there isn't a term to search for. Regrettably.

Very Long Answer:

For references etc see below my complete thought process to arrive at this tentative reply.

allows the name to be used independently of the exact information it represents.

In computing, a variable may be employed in a repetitive process: assigned a value in one place, then used elsewhere, then reassigned a new value and used again in the same way (see iteration)

Call-by-value is not a single evaluation strategy, but rather the family of evaluation strategies in which a function's argument is evaluated before being passed to the function.

but a list (which gets rendered and can be indexed in exactly the same way) is a value.

SO that's multiple values presented as one.... i.e. scalar even though not scalar, but if the program sees it as one value (despite being a list) then the definition of being scalar is met

From Excel Help ROW function:

If reference is a range of cells, and if
ROW is entered as a vertical array,
ROW returns the row numbers of reference as a vertical array.

and from me: if ROW is used WITHIN a formula capable of accepting array input, then (it seems) that there is an assumption that CSE (Control Shift Enter) has been applied by the array input accepting function. This seems to be perfectly sensible: that where a function takes arrays as its input, it uses array output from functions so capable.

I suppose the alternative is to have the array accepting function fail to use (the whole) (potentially) available input array data, fail to perform when suitable data is in fact available to produce design intended output.

As such,

using array capable scalar formulae within array formulae will have the effect of CSE entering said array capable formulae.

using CSE will apply a scalar formula to all the results of an array derived by an array capable formula, whereas not doing so will mean the scalar formula Reads" only the first column and row result of that array.

The above two are illustrated thus:

The illustrated SUMPRODUCT formula is merely to illustrate that its use being an array formula in itself obtains the same processing of results as the SUM formula entered by CSE. If there is a point it seems to me that CSE used on the SUM is in fact activating the array property of the ROW formula within, which is then being evaluated (i.e. summed) as a (one dimensional) list presented to the scalar SUM formula.

Conclusion: no search term. Which explains why I have never found one.

Upon re-reading all this it occurs to me that where you write "scalar functions can be forced to return arrays"; that's not true, by definition. The scalar SUM is returning a single result, which is a scalar result, not an array, despite having evaluated an array, although if that array is only one dimensional then it still fits within scalar as defined by call-by-value.

p.s. edit I think some of the above could be better expressed.. but the overall idea remains, which is that presenting an array derived list to a scalar formula does not change it from a scalar formula to something else. Hence no search term.

I am using 'scalar' in the sense of 'atomic value' / single value. Of course you can have a scalar input that is an array of values. I have tried to be as specific as possible.

I am using 'iteration' in the sense of cycling through the inputs and calling a function repeatedly.

I am glad you also find there is no such term to identify this 'concept' as I was beginning to think I was missing something basic

I didn't mean that the SUM function is presented with a list of single results to add up, rather that in that version of events the ROW function is evaluated for each single cell in the range passed to it, in order to generated a 'temporary' array before it is passed to SUM. So in that sense SUM expects an array and is passed an array - it has no idea the passing function is in fact scalar and has been repeated for multiple cells.

Using the ROW function as an example, and generic code concepts &, I think that if you were to imagine how you would code it yourself, then it would accept a range object. That range object can of course be 1 or more cells so the function code says "if there is more than one cell in the range then take the 1st one". The evaluation logic then cycles through the input range so that it iterates through the cells in the range, passing a single cell each time, and then combines the result values into an array then the consuming 'array function' will be none the wiser as to the origin or the array.

I don't think CSE 'activates the array ability' in scalar functions, I think it seems more sensible and likely that is is an 'instruction to iterate & repeat' as it were. Otherwise that would mean, as I suggested in one of my previous posts, that all functions are array functions. If that were the case then there would be no point in a CSE ability since Excel would always decide what the formula should evaluate to. If you look at all our examples it would always better to assume array and the leave it to the user's input selection to decide the use. If you think about it you would never use an array formula without CSE as the answer is meaningless.

I certain you are right about the presence of a consuming array function casing implicit CSE entry on it's parameter function.

I think that CSE entry (assumed or literal) is basically an override for the default Excel formula evaluation system to say "treat all function inputs and results as arrays if at all possible - if array function consumes a scalar return function with an array input then generate temporary array from scalar function (if it has non-scalar inputs) before passing to the consuming function".

The only other option is that that internally the coding uses 'function overloading' and one version works with scalar inputs and one with arrays. CSE (direct or implied) would therefore indicate the use of the array version of the function. That would mean that those functions documented as 'array functions' simply have the default version of the function set to the array rather than the scalar.

As I said before I think where this becomes confusing; in that 'array functions' take arrays as inputs and 'array formulas' output arrays, but there is no (non-confusing) 'terminology' to differentiate from functions that output single values and those that output arrays (of course an 'array formula' is an outer function which returns an array). Of course an array function (as in 'SUM') can generate a scalar result.

Summary:

You have confirmed my understanding that there is no term for this concept

There is no way to infer how this ability is really coded (and hence understand the rules) - the results will be the same in either case. In makes no difference really.

The use of CSE and the idea of array formulas is a great deal more complex and fine-lined than is generally understood or documented

I'm still pondering our exchange above and re-reading everything.

So this is an aside which has me perplexed, you write in your op the following:

The formula below will return an array of number for each row in the range. The array 10,11,12,13,14,15,16,17,18,19

(ROW(A10:A20))

teylyn then writes (amongst much else with examples):

The expression (ROW(A10:A20)) only returns an array of the numbers 10 to 20 if the formula it is used in is either array entered with Ctrl-Shift-Enter, or if the formula it is used in is in itself an array formula.

In your text you are concluding that the addition of a pair of brackets will result in 10,11,12,13,14,15,16,17,18,19

What you do not say is exactly how you achieve that result (far as I can see).
You don't say it is with CSE, in fact I think you imply (say?) it is not, by postulating that the additional bracket pair does that job.

So I tried to replicate your result.

Without CSE, to get that list (array) it is necessary to copy/drag/fill down the next 9 cells to populate them, which indeed does result in 10,11,12,13,14,15,16,17,18,19

Here's the rub, the copy down process increments the first array position result by 1 each time: see image below.

Whereas the CSE entry method increments the position and shows that result. i.e. it gives the results of index locations 1 2 3 4 5 6 7 8 9 10 , which are indeed 10,11,12,13,14,15,16,17,18,19

These two sets of results are the same only by co-incidence.
That's what has me perplexed.

My apologies, in the initial post I had extracted the "(ROW(A10:A20))" form the middle of another formula (that used the 'index' array function I think) based on the understanding that as CSE was not being used that brackets must be forcing Excel to evaluate the contained reference different.

It was a rather misguided and confusing way of introducing the discussion on how Excel can apply CSE logic to a non CSE formula. I had assumed that the use of brackets were causing Excel's logic to change.

Not a completely unfounded assumption since a) changing the position of the brackets changes the context b) you very often see Excel formulas add in brackets around a range reference where they do not see logically required and appear to have no function.

thanks - a fascinating question in any case. Has taught me a great deal. Shame we can't give points for a good question :-)
I will re-read with your remarks in mind.
edit: so if you were to ask your question over again, in view of this experience how would you word it?

'anthonymellorfca' : I think it would be "why is Ctrl+Shift+Enter necessary rather than the evaluation method used and the return value generated decided based on the entered context ?"

After all a formula of "SUM(row(A1:A10)" would make no sense at all (apart from a mistake) being executed in a scalar context (where ROW processes the 1st cell) because SUM is an array function. It only makes sense that that formula should be executed in array context - in other words CSE should be assumed (without using CSE or embedding the scalar result function in an array function like INDEX).

Although I don't think, in light of our conclusions, I will be able to search directly I still want to know what functions are scalar only and which have 'array potential'. As I said previously it appears like thanks to implied CSE and function iteration all functions can act as array functions (i.e. they can appear to accept arrays).

.. it appears that Excel cannot resolve this as an array as it is an 'unequal array'. If you use INDEX (around 'C3:C7*6') to accept and return an array or use CSE, then it does work.

I can't see why the SUM array function can't 'force' 'C3:C7*6' to produce a equal array whereas INDEX and CSE can.

it's still an array.

Entered like this and array entered in one cell, the formula

=(ROW(A10:A20))

will still return just the first result of the array, i.e. the value 10. If several cells are selected and the formula is entered into the first cell and then array entered, the second cell will show the value 11, the next one 12, etc. The outer round braces can be omitted for that exercise.

The expression (ROW(A10:A20)) only returns an array of the numbers 10 to 20 if the formula it is used in is either array entered with Ctrl-Shift-Enter, or if the formula it is used in is in itself an array formula.

Compare

=sum(ROW(A10:A20)) with simple Enter

=sum(ROW(A10:A20)) with Ctrl-Shift-Enter

and

=sumproduct(ROW(A10:A20)) with simple Enter.

Sumproduct() is inherently an array function and will interpret (ROW(A10:A20)) as an array, even if confirmed with a simple Enter. Sum() however, is not an array function and to make it regard (ROW(A10:A20)) as an array instead of just the first value, it needs to be array entered.

There are many possibilities to exploit this behaviour and help create shorter and more concise formulas.

Also of note is that named formulas, i.e. the formulas entered to define a named range are

alwaysarray formulas. A named range "MyRange" with the formula=SUM(ROW(Sheet1!A10:A20))

Entered into a worksheet cell like

=MyRange

this will return the sum of the row numbers. If the range is defined with relative references, as in the example, the result will be different, depending on what row the =MyRange name is called upon. This can be a VERY powerful tool, since very often, helper calculation cells in worksheets can instead be carried out by such range names.

Good resources for learning more about arrays are Chip Pearson's site and Daniel Ferry's ExcelHero site.

http://www.cpearson.com/Excel/MainPage.aspx

http://www.excelhero.com/

cheers, teylyn