2.4 WS array constant



An array constant in a formula or defined name (RefersTo):


1. An array constant in a WS formula


Examples of row, column, and multi-dimensional array constants are shown here:


To enter the formula, select a WS range of the correct dimension, type in the formula, then press the Control + Shift + Enter key combination. An array constant with a single element is possible but unnecessary.


2. An array constant as a defined name


Continuing with the example from figure 1, display the New Name dialog box, then paste the array constant to the Refers To: panel (figure 2). Ensure that the string commences with the formula equals (=) character.

The range shown in figure 1 is named ThreeX4, and has been moved 3 columns to the right. The Name Manager view in figure 3 shows the details for the array name and array ThreeX4 constant ThreeByFour.

3. Examples of WS array constants

3.1 WS SUBTOTAL function

Reference: Data analysis :: subtotals

 
Name: ListSubTotal
RefersTo: ={"average",101,1;"count",102,2;"counta",103,3;"max",104,4;"min",105,5;"product",106,6;"stdev",107,7;"stdevp",108,8;"sum",109,9;"var",110,10;"varp",111,11}
 
=SUBTOTAL(VLOOKUP(RC[-1],ListSubTotal,2,FALSE),Data) + N("Excludes hidden values")
 
Name: Stats5x1
RefersTo: ={"Average","StDev","Min","Max","Count"}
 

3.2 TAX table array constant

Reference: Arrays, and sub-arrays, with application to tax rates

 
Name: TaxArray1516
RefersTo: ={0,0,"0%";18201,0,"19.0%";37001,3572,"32.5%";80001,17547,"37.0%";180001,54547,"45.0%"}
 
=VLOOKUP(RC[-1],TaxArray1516,2,TRUE)
+(RC[-1]-VLOOKUP(RC[-1],TaxArray1516,1,TRUE)+1)
*VLOOKUP(RC[-1],TaxArray1516,3,TRUE)