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

multiitem_excel_formulas.py

'''
This script serves as and answer to the stack overflow question
https://stackoverflow.com/questions/59652623/when-does-excel-use-a-single-item-from-a-named-range-v-s-all-the-items

'''

import urllib.request
from path import Path
import collections

class ddict(dict):    
    def __init__(self, **kwds):
        self.update(kwds)
        self.__dict__ = self
    

#%% I manually added //True to variables with singlelike behaviour
#From https://github.com/LibreOffice/core/blob/0cb4b2a18b790ff74a6893781beba2c4a4a8ce8f/sc/source/filter/excel/xlformula.cxx
types = '''
#define RO   { EXC_PARAM_REGULAR,   EXC_PARAMCONV_ORG, false }  // True
#define RA   { EXC_PARAM_REGULAR,   EXC_PARAMCONV_ARR, false }  // 0
#define RR   { EXC_PARAM_REGULAR,   EXC_PARAMCONV_RPT, false }  // True
#define RX   { EXC_PARAM_REGULAR,   EXC_PARAMCONV_RPX, false }  // 0
#define VO   { EXC_PARAM_REGULAR,   EXC_PARAMCONV_ORG, true  }  // True
#define VV   { EXC_PARAM_REGULAR,   EXC_PARAMCONV_VAL, true  }  // True
#define VA   { EXC_PARAM_REGULAR,   EXC_PARAMCONV_ARR, true  }  // 0
#define VR   { EXC_PARAM_REGULAR,   EXC_PARAMCONV_RPT, true  }  // True
#define VX   { EXC_PARAM_REGULAR,   EXC_PARAMCONV_RPX, true  }  // 0
#define RO_E { EXC_PARAM_EXCELONLY, EXC_PARAMCONV_ORG, false }  // True
#define VR_E { EXC_PARAM_EXCELONLY, EXC_PARAMCONV_RPT, true  }  // True
#define C    { EXC_PARAM_CALCONLY,  EXC_PARAMCONV_ORG, false }  // True
'''.strip()


#%% parse into a mapping
varmap = {}
for line in types.split('\n'):
    lst = line.split()
    varmap[lst[1]] = eval(lst[-1])
    

#%% Read the source files where the opcodes and functions are described
formulastxt = urllib.request.urlopen('https://raw.githubusercontent.com/LibreOffice/core/1d10db1003f092f48ed09a0d45994f7481b96d84/sc/source/filter/oox/formulabase.cxx').read().decode('utf-8')
    

#%% These table are in other spreadsheet software and not in Excel
exclude_tables = {'saFuncTableOdf','saFuncTableOOoLO'}


#%% Get all the excel functionname table entries 
macrolines = []
funclines  = []
for i in formulastxt.split('const FunctionData saFunc')[1:]:
    tablename = 'saFunc'+i.split('[')[0]
    if tablename in exclude_tables: 
        continue
    
    tablecontent = i.split('\n{')[1].split('\n};')[0] 
    tablecontent = '\n'.join([i for i in tablecontent.split('\n') if i.strip().split('//')[0] != ''])
 
    
    for line in tablecontent.split('\n'):
        if not '{' in line: 
            continue
        line = line.replace('{', " ")
        line = line.split('//')[0]
        if line.strip().startswith('nullptr'):
            continue
    
        funclines.append([i.strip() for i in line.split('}')[0].split(',')])
    
    
    
#%% Make a dict with functions and their argumnent information
funcdict = {} #collections.defaultdict(list)
for func in funclines:
    
    _min=int(str(func[4]).replace('MX', '255'))
    _max=int(str(func[5]).replace('MX', '255'))

    
    rettype=func[6]
    argtypes=func[7:]
    
    for i in range(len(argtypes)):
        argtypes[i] = argtypes[i].replace('_E', '')
        
    if argtypes == ['']: 
        argtypes = []
        argsingle = []
    else:   
        argsingle = [varmap[i]==True for i in argtypes]

    func[1] = func[1].replace('"',"").strip()
    funcdict[func[1]] = ddict(min = _min,
                              max = _max,
                              rettype = rettype,
                              argtypes = argtypes,
                              argsingle = argsingle)

#Fix this out-of-whack one
funcdict['AGGREGATE'] = ddict(**{'min': 3,
 'max': 255,
 'rettype': 'V',
 'argtypes': ['VR', 'RO', 'RX'],
 'argsingle': [True, True, False]})
            
            
#%% Find all functions with any vectorlike arguments
for fname in sorted(funcdict.keys()):
    args = funcdict[fname]
    if args.max == 0: continue

    arrayargs = []
    for i, t in enumerate(args.argtypes):
        if not(varmap[t]):
            arrayargs.append(i+1)

    #print the function            
    if arrayargs:
        print(fname +
              ' ( ' +
              ', '.join([v if i+1 in arrayargs else '□' for i,v in enumerate(args.argtypes)]) +
              ('' if len(args.argtypes) == args.max else ', …') +
              ' )'
              )
              

Output:

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, □, … )