When does excel use a single item from a named range v.s. all the items?

Question

Say you have the following named-ranges a, b, c, in column A, B, and C:

   |A  |B  |C
---+---+---+---------
1  |a  |b  |c 
---+---+---+---------
2  |1  |=a |=b+a
---+---+---+---------
3  |2  |=a |=b+a
---+---+---+---------
4  |3  |=a |=CONCATENATE(b+a)
---+---+---+---------
5  |4  |=a |=MIN(a)

then B4=3 and C4="33" for instance are calculated from a single cell in their named-range reference. C5=1 on the other hand is calculated using the whole range over a.

I would like to know where this behavior is defined and how can I know when and how it is used. Is this a case-by-case type of thing or is the behavior well-defined?

Answer 1

I got the answer, or at least captured a large collection of the answer. It seems like the single-item behaviour works on functions that has arguments that are single-variable like (so no ranges or vectors).

To find these functions, I wrote a script that inspects some files from the LibreOffice source code, and try to map all the Excel functions (that are listed by the project) with arrays or ranges as arguments.

Here's the script: https://gist.github.com/heetbeet/64cdfce26c2e4420ed416028b1d22393

And here's the output. All functions are listed with single-item arguments left blank, and multi-item arguments filled with their abbreviated type:

AGGREGATE ( □, □, RX, … )
AND ( RX, … )
AVEDEV ( RX, … )
AVERAGE ( RX, … )
AVERAGEA ( RX, … )
CHISQ.TEST ( VA, … )
CHITEST ( VA, … )
CORREL ( VA, … )
COUNT ( RX, … )
COUNTA ( RX, … )
COVAR ( VA, … )
COVARIANCE.P ( VA, … )
COVARIANCE.S ( VA, … )
DEVSQ ( RX, … )
F.TEST ( VA, … )
FORECAST ( □, VA, … )
FORECAST.ETS ( □, VA, □, … )
FORECAST.ETS.CONFINT ( □, VA, □, … )
FORECAST.ETS.SEASONALITY ( □, VA, □, … )
FORECAST.ETS.STAT ( □, VA, □, … )
FORECAST.LINEAR ( □, VA, … )
FREQUENCY ( RA, … )
FTEST ( VA, … )
FVSCHEDULE ( □, RX )
GCD ( RX, … )
GEOMEAN ( RX, … )
GROWTH ( RA, RA, RA, □ )
HARMEAN ( RX, … )
IMPRODUCT ( RX, … )
IMSUM ( RX, … )
INDEX ( RA, □, … )
INTERCEPT ( VA, … )
IRR ( RA, □ )
ISREF ( RX )
KURT ( RX, … )
LARGE ( RX, □ )
LCM ( RX, … )
LINEST ( RA, RA, □, … )
LOGEST ( RA, RA, □, … )
LOOKUP ( □, RA, … )
MATCH ( □, RX, □ )
MAX ( RX, … )
MAXA ( RX, … )
MDETERM ( VA )
MEDIAN ( RX, … )
MIN ( RX, … )
MINA ( RX, … )
MINVERSE ( VA )
MIRR ( RA, □, … )
MMULT ( VA, … )
MODE ( VA, … )
MODE.MULT ( VA, … )
MODE.SNGL ( VA, … )
MULTINOMIAL ( RX, … )
NETWORKDAYS ( □, □, RX, □, … )
NETWORKDAYS.INTL ( □, □, □, RX )
NPV ( □, RX, … )
OR ( RX, … )
PEARSON ( VA, … )
PERCENTILE ( RX, □ )
PERCENTILE.EXC ( RX, □ )
PERCENTILE.INC ( RX, □ )
PERCENTRANK ( RX, □, □ )
PERCENTRANK.EXC ( RX, □, □ )
PERCENTRANK.INC ( RX, □, □ )
PROB ( VA, VA, □, … )
PRODUCT ( RX, … )
QUARTILE ( RX, □ )
QUARTILE.EXC ( RX, □ )
QUARTILE.INC ( RX, □ )
RSQ ( VA, … )
SERIESSUM ( □, □, □, RX )
SKEW ( RX, … )
SKEW.P ( RX, … )
SLOPE ( VA, … )
SMALL ( RX, □ )
STDEV ( RX, … )
STDEV.P ( RX, … )
STDEV.S ( RX, … )
STDEVA ( RX, … )
STDEVP ( RX, … )
STDEVPA ( RX, … )
STEYX ( VA, … )
SUM ( RX, … )
SUMPRODUCT ( VA, … )
SUMSQ ( RX, … )
SUMX2MY2 ( VA, … )
SUMX2PY2 ( VA, … )
SUMXMY2 ( VA, … )
T.TEST ( VA, VA, □, … )
TREND ( RA, RA, RA, □ )
TRIMMEAN ( RX, □ )
TTEST ( VA, VA, □, … )
TYPE ( VX )
VAR ( RX, … )
VAR.P ( RX, … )
VAR.S ( RX, … )
VARA ( RX, … )
VARP ( RX, … )
VARPA ( RX, … )
WORKDAY ( □, □, RX, □, … )
WORKDAY.INTL ( □, □, □, RX )
XIRR ( RX, RX, □ )
XNPV ( □, RX, RX )
XOR ( RX, … )
Z.TEST ( RX, □, … )
ZTEST ( RX, □, … )