VSTACK  & VSTACKF Functions

VSTACKF is an alias of FastExcel VSTACK. Creates a single array from multiple arrays or ranges by appending up to 29 Range /Array arguments below one another row wise. Missing cells in the output array can be filled with Fill characters, for example 0.0 or "".

VSTACK is a multi-threaded, non-volatile array function.

VSTACK & VSTACKF Syntax

VSTACK ([Fill], Arg1, Arg2, … , Arg28) & VSTACKF ([Fill], Arg1, Arg2, … , Arg28)

Fill (Optional, default #N/A)

A cell or character or digit to be used to fill unused cells in the output array, for example 0.0 or “”.
When the Arrays or ranges to be appended do not have equally sized dimensions the gaps will be filled with the Pad character. Use zero if you want to append differently-sized numeric arrays and pass the output to an aggregating function such as SUM() or a single-cell array formula.

Note: A non-array argument given for the first argument to VSTACK will be treated as a fill value. Preface the non-array argument with a comma to make it the first VSTACK item.

Arg1 to Arg28

Ranges or arrays to be appended. Each Range/Array is placed in the output array starting in column 1 of the next available output row.

The ranges to be appended can be on different sheets.

If an Argument range/array is a constant or resolves to a single value it will be propagated across the entire row.

With the introduction of the native Excel VSTACK function VSTACKF allows you to choose either the native or FastExcel VSTACK implementation.

VSTACK Examples

A2:C2 contains 1,2,3
B4:D4 contains 4,5,6

{=VSTACK(A2:C2,B4:D4)} array entered into O20:Q21 returns

(A2:C2 cannot be a PAD character so is treated as Arg1)

{=VSTACK(0,A2:C2,{100;200;300},B4:D4)} array entered into C23:F28 returns

The column dimension of the output array is the maximum number of columns in any of the arguments. The row dimension of the output array is the sum of the number of rows in each argument. So the output array is 5 rows deep by 3 columns, which Excel has expanded with #N/A to the number of rows (6) and columns (4) the array formula was entered into.

The values from each argument are placed in turn into the output array, appending row-wise to the bottom.

For the arguments that have less than 3 columns the missing rows are filled with the pad character (zero in this case). Because the formula was array-entered into 6 rows by 4 columns Excel has added #N/A in the 6^th row and 4^th column.

Entering =SUM(VSTACK(A2:C2,{100;200;300},B4:D4)) (NOT as an array formula) returns #N/A, because the array returned from ROW.ARRAY contains #N/A.

Entering =SUM(VSTACK(0,A2:C2,{100;200;300},B4:D4)) (NOT as an array formula) returns 621, which is the sum of the values in the output array (the #N/A in the arrayhas been replaced by zero).

Entering =AVERAGE(VSTACK(0,A2:C2,{100;200;300},B4:D4)) (NOT an array formula) returns 41.4 which is 621 divided by the number of cells in the output array (15).

Copyright © 2023 Decision Models Ltd