NxFlatten - collapse a data table into a flat 3-columns form.

Flattens (or collapses) a 2-dimensional table or matrix into a flat 3-column (X, Y, Z) representation.

Syntax

NxFlatten(Table, X-Levels, Y-Levels, KeepNA)

Table

is a 2-dimensional array on a worksheet for the dependent variable (Z), such that rows define different levels/values of X (1st variable) and columns define different levels/values of Y (2nd variable). 

X-Levels

is a one-dimensional array of cells (e.g., rows or columns) for corresponding levels/values of X (1st variable) in the data table.

Y-Levels

is a one-dimensional array of cells (e.g., rows or columns) for corresponding levels/values of Y (2nd variable) in the data table.

KeepNA

is a flag for leaving (or purging) observations with missing values (e.g., blank, #N/A, #VALUE!, #NUM!, empty cell) in the input data set. If missing or omitted, keepNA is assumed to be False.

Remarks

1. The NxFlatten function must be entered as an array formula in a range with three columns and rows equal to the original table rows times its columns (minus any missing data point if KeepNA is False).
2. Use NxFlatten to transform the tabular form representation of a three-dimensional data set into a flat 3-column (X, Y, Z) format.
3. The observations in X, Y, and Z can have any order and are not assumed to be sorted.
4. The data "Table" array may have missing values (#N/A). NxFlatten keeps/removes those entries based on the "KeepNA" flag setting only if corresponding X-Level and Y-Level values are given (i.e., not missing).
5. The X-Levels and Y-Levels may have missing values (#N/A). In this case, NxFlatten will remove those entries.
6. The X-Levels and Y-Levels values can have any order. No sorting is required.
7. The NxFlatten() function is available starting with version 1.66 PARSON.

Files Examples

References

• John Walkenbach (2002). Excel charts. Wiley. ISBN 978-0764517648.