≡ ▼
Work in Progress :: Please contact us to report errors, typos, etc.

This article proposes a method for working with what I'm calling a Data Structure Array in Excel or simply "Structure" for short. There may be an infinite number of ways to organize data within a 2-dimensional grid. So, this is just one possible method.

WHY? What is the Need? As I've been working with LAMBDA functions in Excel, I keep coming across the desire to (a) output more than one result, (b) store multiple arrays in a single variable and (c) work with arrays that have more than two or three dimensions.

The idea for this type of data structure stems mainly from my experience using the struct function for the "structure array" in MATLAB. There are similarities, but they do not function exactly the same way. The approach described here is based on the use and availability of certain Excel functions.

Sample Data Structure Arrays

The example below shows a very simple structure that consists of three named sets of data which we will call fields to stick with common terminology. "Title", "Chars" and "Numbers" are the names of these 3 fields. Color has been added only to help identify the main attributes of the structure.

Sample Data Structure Array in Excel

Attributes of a Structure

Field Names: The left column of the structure is used only for the names of each field and must be unique because we'll be using the MATCH function in Excel to get fields from the structure.

Field Size: Columns 2 and 3 in the rows containing the field names are the SIZE of the element (ROWS x COLUMNS). A field can be a minimum of size 1x1 and a maximum determined by memory and array size limits in Excel (whatever that might be).

Data: The array defined by the field starts in the row immediately below the name and size in column 2.

Nested Structures

A field can itself be another structure, and there is no built-in limit to how many structures may be nested. You can refer to field "ii" within field "B" using the array {"B","ii"}. Or, if you are using indices for the names of the arrays, the array {2,5,3} would refer to field 3 within field 5 within field 2.

The example below shows a portion of what is essentially a 4-dimensional structure (x,y,z plus time, t) representing the surface z = x^2+y^2-t^2 for t={1,2,...}.

Example Nested Structure Array in Excel

In the above example, field {2,"z"} would refer to the 3x3 matrix {-2,1,6;1,4,9;6,9,14}

Functions for Working with Structures

We may expand on this list over time, or continue to develop how these functions work, so the function is not provided on this page. Instead, you may download the sample file to access and use the following LAMBDA functions.

  • STRUCT_GET(name,structure) :: Retrieve a field by name from an existing structure
  • STRUCT_CREATE(name,value,[name_2],[value_2],...]) :: Create a structure with up to 4 name/value pairs.
  • STRUCT_APPEND(structure,name,value) :: Append a new field to an existing structure.
  • STRUCT_REMOVE(name,structure) :: Remove a field by name from an existing structure.

STRUCT_GET

=STRUCT_GET(name, structure)

This function retrieves a field from a structure by giving it the name of the field. The name should be the same data type (number or string) used for the field name in the structure.

In the Nested Structure example above, STRUCT_GET({1,"z"}) returns {1,4,9;4,7,12;9,12,17}. STRUCT_GET(2) returns the field named 2 which is the 12x4 structure containing fields "x","y","z".

▼ LAMBDA Function Code

STRUCT_CREATE

=STRUCT_CREATE(name_1, value_1,...,[name_4],[value_4])

Create a structure by specifying up to 4 name/array pairs. You can add more via STRUCT_APPEND. The example shown in the first image above can be created using the following:

LAMBDA Function Code for AFE Workbook Module

=LET(
    chars,{"A","B";"C","D";"E","F"},
    numbers,{100,1,10;200,2,20;300,3,30},
    STRUCT_CREATE("Title","Structure Example","Chars",chars,"Numbers",numbers)
)

▼ LAMBDA Function Code

STRUCT_APPEND

=STRUCT_APPEND(structure,name,value)

This function appends a new field to an existing structure. The first example above can be created by either calling STRUCT_APPEND in succession, or through nesting:

=LET(
    chars,{"A","B";"C","D";"E","F"},
    numbers,{100,1,10;200,2,20;300,3,30},
    struct_1,STRUCT_CREATE("Title","Structure Example"),
    struct_2,STRUCT_APPEND(struct_1,"Chars",chars),
    struct_3,STRUCT_APPEND(struct_2,"Numbers",numbers),
    struct_3
)

=LET(
    chars,{"A","B";"C","D";"E","F"},
    numbers,{100,1,10;200,2,20;300,3,30},
    STRUCT_APPEND(
        STRUCT_APPEND(
            STRUCT_CREATE("Title","Structure Example"),
            "Chars",chars
        ),
        "Numbers",numbers
    )
)

▼ LAMBDA Function Code

STRUCT_REMOVE

=STRUCT_REMOVE(name, structure)

This function will remove a field by name. It currently only removes a field from the first dimension of a nested structure. In other words, name can only be a single value.

▼ LAMBDA Function Code

Examples

Example 1 - Create a 4-D Structure (x,y,z, plus time, t)
This example shows how the Nested Structure example above can be generated using a combination of other LAMBDA functions and a REDUCE loop. 
Test: Copy and Paste this LET function into a cell
=LET(
    xvec, L_SE(1, 3),
    yvec, L_SE(1, 3),
    tvec, L_SE(1, 2),
    xgrid, L_MESHGRID(xvec, yvec, "X"),
    ygrid, L_MESHGRID(xvec, yvec, "Y"),
    construct, REDUCE("",tvec,
        LAMBDA(acc, t,
            LET(
                zgrid, xgrid ^ 2 + ygrid ^ 2 - t ^ 2,
                internal, STRUCT_CREATE("x", xgrid, "y", ygrid, "z", zgrid),
                IF(t=INDEX(tvec,1,1), STRUCT_CREATE(t, internal), STRUCT_APPEND(acc, t, internal))
            )
        )
    ),
    construct
)
Example Nested Structure Array in Excel
Example 2: 2D X-Y Grid with Calculated Z
This example creates an X-Y Grid then calculates z = x^2+2*y^1.2-x*y 
=LET(
    xvec, L_LINSPACE(1, 3, 5),
    yvec, L_LINSPACE(1, 4, 5),
    xgrid, L_MESHGRID(xvec, yvec, "X"),
    ygrid, L_MESHGRID(xvec, yvec, "Y"),
    zgrid, xgrid^2 + 2*ygrid^1.2 - xgrid*ygrid,
    STRUCT_CREATE(
        "X", xgrid,
        "Y", ygrid,
        "Z", zgrid
    )
)
Example 3: Store data for a plot
This example shows how you can store all the data for a 2D plot in a single structure. The purpose of these structures is to be able to pass them between other LAMBDA functions. However, this is an example of storing data within a structure. 
=LET(
    xvec, L_LINSPACE(0, 2 * PI(), 10),
    yvec, SIN(xvec),
    STRUCT_CREATE(
        "Title", "Y = SIN(X)",
        "X", TRANSPOSE(xvec),
        "Y", TRANSPOSE(yvec)
    )
)
References & Resources
Disclaimer: This article is meant for educational purposes only. See the License regarding the LAMBDA code, and the site Terms of Use for the documentation.

Follow Us On ...

FB  PIN  TWEET  LI  IG  YT 
Excel and Financial Modeling Conference
Template Categories