|
HSTACK & HSTACKF Functions
HSTACKF is an alias of HSTACK, created to avoid the native Excel HSTACK. Creates a single array from multiple arrays or ranges by appending up to 29 Range /Array arguments alongside one another column wise. Missing cells in the output array can be filled with Fill characters, for example 0.0 or "".
HSTACK is a multi-threaded, non-volatile array function.
HSTACK & HSTACKF Syntax
HSTACK ([Fill], Arg1, Arg2, … , Arg28) or HSTACKF ([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 or “”.
When the Arrays or ranges to be appended do not have equally sized dimensions the gaps will be filled with the Fillcharacter. 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 HSTACK will be treated as a fill value. Preface the non-array argument with a comma to make it vthe first HSTACK item.
Arg1 to Arg28
Ranges or arrays to be appended. Each Range/Array is placed in the output array starting in row 1 of the next available output column.
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 down the entire column.
With the introduction of the native Excel HSTACK function HSTACKF allows you to choose either the native or FastExcel HSTACK implementation.
HSTACK Examples
A2:C2 contains 1,2,3
B4:D4 contains 4,5,6
{=HSTACK(A2:C2,B4:D4)} array entered into C20:H20 returns
.png)
(A2:C2 cannot be a PAD character so is treated as Arg1)
{=HSTACK(0,A2:C2,{100;200;300},B4:D4)} array entered into C7:J10 returns
.png)
The row dimension of the output array is the maximum number of rows in any of the arguments. The column dimension of the output array is the sum of the number of columns in each argument. So the output array is 3 rows deep by 7 columns.
The values from each argument are placed in turn into the output array, appending column-wise to the right.
For the arguments that have less than 3 rows the missing rows are filled with the fill character (zero in this case). Because the formula was array-entered into 4 rows by 8 columns Excel has added #N/A in the 4th row and 8th column.
Entering =SUM(HSTACK(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.
Entering =AVERAGE(HSTACK(0,A2:C2,{100;200;300},B4:D4)) (NOT an array formula) returns 29.57 which is 621 divided by the number of cells in the output array (21).
|
