DC-Den

One function in Excel that I find useful is the Descriptive Statistics from the Analysis Toolpak Add-In. At a glance I see a summary of the sample data. However I also find it cumbersome to use. It requires you to add-in Analysis Toolpak - not loaded by default. I wonder why Microsoft choses not to pre-load in Excel even though it has been available since Excel 2000 (maybe even earlier?). It launches a dialog. Recently, this becomes an issue as it unavailable in Excel Web. It returns a static result, not an auto recalculating function, i.e. the result is not updated if and when the data is updated. Of the 3 reasons above, I find the last most annoying. While field/laboratory sample data does not change after collection, but input data from external sources can be changing frequently. Any report based on external sources would have to be painstakingly updated manually. So in this blog, I will create a Descriptive Statistics function with LAMBDA that would recalculate automatically just

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 s