Solved

Excel 'multiple result' formulas

Posted on 2013-05-31
22
372 Views
Last Modified: 2013-08-08
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

ROW(A10:A20)

Open in new window


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

Open in new window


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 ?
0
Comment
Question by:AL_XResearch
  • 10
  • 4
  • 4
  • +1
22 Comments
 
LVL 50

Expert Comment

by:teylyn
ID: 39210147
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.

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

cheers, teylyn
0
 
LVL 3

Author Comment

by:AL_XResearch
ID: 39210218
Thanks 'teylyn' for the quick response, the examples and links.

I know Chip Pearson's site of old but it does not cover what I am asking.

I am not asking about array formulas or array functions - I am familiar with both.


Take another common formula to find the last cell with a specific value in a specific range as an example:

INDEX(MAX((F13:F19=E13)*(ROW(F13:F19))),0,0)

Open in new window


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

Array Formulas

It is hard to ask about a topic when you don't have a name to refer to it by.
0
 
LVL 50

Expert Comment

by:teylyn
ID: 39210256
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 39210264
FYI the brackets are irrelevant in that formula.
=INDEX(MAX((F13:F19=E13)*ROW(F13:F19)),0,0)
works just the same.
0
 
LVL 3

Author Comment

by:AL_XResearch
ID: 39210315
Thanks 'teylyn' ...

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.
0
 
LVL 50

Expert Comment

by:teylyn
ID: 39210329
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.

It's not the brackets. It's the context.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 39210352
It's not the brackets.

Yes - that is the point I was trying to make too. :)
0
 
LVL 3

Author Comment

by:AL_XResearch
ID: 39210361
But that is my point....

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
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 39210444
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.
0
 
LVL 50

Expert Comment

by:teylyn
ID: 39210484
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.
0
 
LVL 3

Author Comment

by:AL_XResearch
ID: 39210886
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 ?
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 85

Expert Comment

by:Rory Archibald
ID: 39211089
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.
0
 
LVL 9

Expert Comment

by:anthonymellorfca
ID: 39213163
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:

scalar formula that loops within its own execution?
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.
0
 
LVL 3

Author Comment

by:AL_XResearch
ID: 39213644
Thanks 'anthonymellorfca' for your thoughts.

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):
SUM(ROW(DATA))

Open in new window

... returns '1' , whereas ...
SUM(INDEX(ROW(DATA),0,0))

Open in new window

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

Also consider; if you enter the array-formula ...
{ SUM(ROW(DATA)) }

Open in new window

... 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).
0
 
LVL 9

Expert Comment

by:anthonymellorfca
ID: 39214177
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.


Here is my thinking so far:

http://en.wikipedia.org/wiki/Scalar

Scalar (computing), an atomic quantity that can hold only one value at a time

http://en.wikipedia.org/wiki/Scalar_(computing)

 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)

like this: my idea of iteration in this context

While a variable's name, type, and location often remain fixed, the data stored in the location may be changed during program execution.

http://en.wikipedia.org/wiki/Evaluation_strategy

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:

CSE versus Scalar comparison
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.
0
 
LVL 3

Author Comment

by:AL_XResearch
ID: 39215820
Thanks 'anthonymellorfca' for the lengthy reply.

I think we are basically in agreement.

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
0
 
LVL 9

Expert Comment

by:anthonymellorfca
ID: 39216330
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.

Here's what I am seeing (quite literally):

Copy v CSE v Absolute
I cannot see brackets making a difference here.

Anthony
0
 
LVL 3

Author Comment

by:AL_XResearch
ID: 39216420
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.

Sorry about that.
0
 
LVL 9

Accepted Solution

by:
anthonymellorfca earned 500 total points
ID: 39216845
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?
0
 
LVL 3

Author Comment

by:AL_XResearch
ID: 39217139
'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).
0
 
LVL 3

Author Comment

by:AL_XResearch
ID: 39217175
Even array functions cannot always 'force' excel to pass it an array when passed a range.

Take the following formula - which will produce a normal result:
SUM(C3:C7)

Open in new window

An adaptation to that formula intended to multiply each cell in the range by 6 - it will produce #VALUE ...
SUM(C3:C7*6)

Open in new window

.. 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.
0
 
LVL 3

Author Closing Comment

by:AL_XResearch
ID: 39393369
No actual answer, more confirmation, but a very enlightening discussion.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now