Creating Table Arrays with LAMBDA

Recap from the last blog, our intention is to build various custom statistical functions in Excel using the LAMBDA feature.

This week I will create a Table Array using LAMBDA. Excel has VSTACK and HSTACK formulas to create Arrays. Combining these two functions we can build Tables Arrays. We will use Table Arrays to display statistical information.

For a start I will create a simple About summary. Create a new formula with the following details:

Name: dcrAbout

Comments: Returns a summary about dcRacoon Statistics.

Refers To: 

=LAMBDA(

    HSTACK(

      VSTACK(

        "Author",

        "Version",

        "Last Update",

        "Web"

      ) ,

      VSTACK(

        "DC Racoon",

        "0.1",

        "21-Dec-2022",

        "https://dc-den.blogspot.com"

      )

    )

)

The HSTACK creates a horizontal array (columns). And the VSTACK creates vertical arrays, the first being the items in each row, and the second the description for each.

The below screenshot is the result of making a call to this LAMBDA function.

Some points worth noting:

• Write LAMBDA functions in multiple lines. This improves legibility. While LAMBDA function could be written in a single line, troubleshooting becomes nearly impossible for complex implementation, especially multi variable and steps functions. 
• Indentation is necessary. This is also for legibility. Using spaces is better than tabs, as tabs stretches the codes. When programming I usually use tabs, but with LAMBDA, I found 2-space indentation suitable.
• Choose Function and Variable names that are self-descriptive. This improves readability. I found choosing appropriate names was not a simple tasks, and I had to repeatedly return to rename function names and variables.

Things to watch out:

• Missing Commas ",". This is my most common syntax problem. Having multiple lines helped me spot my mistakes.
• Unbalance Brackets "(" and ")". The next common mistake is unbalance brackets, i.e. more "(" than ")". Indentation also helped me spot my mistakes.
• Avoid lazy variable naming. It makes reading difficult. Even intermediate step variables should be named appropriately. I remember using sd as a variable name and later returning to rename as stdev so that it is makes more sense.

Aids:

• Helping Steps. You can write LAMBA functions directly in Excel cells to test the functions first. This provides intermediate results which can be verified, before putting all the steps within a single LAMBDA function.
• Documenting in Text file. I found documenting in a text file easier than in Excel. Debugging the syntax is easier this way. I also used a proper text editor like SciTE, though you could get by using Windows Notepad.
• Consider Snake Case when naming variables. I started with camel case, e.g. expectedValue, but found this harder to read in LAMBDA, so I switched to snake case instead, e.g. expected_value.

Now go out and create table arrays in your DC-DEN!