flex your data

The lambda described in this post is in the LAMB namespace, the gist for which can be found here. The goal TRANSPOSE is great. But sometimes it doesn’t do everything I’d like.  So, the goal here is: Create a lambda function that will rotate an array by 90 degrees an arbitrary number of times  A […]

The gist for this namespace can be found here.  You can download a workbook containing the sample data (sulphates column from Kaggle wine quality dataset), the LAMB namespace, and the OUTLIER namespace here. The goals This is the second of a two-part blog post covering some work I’ve been doing to update and improve some […]

The gist for this namespace can be found here.  The goals When I was first learning about LAMBDA in Excel, I wrote some functions to calculate outlier tests against a column of data.  The main function – OUTLIER.TESTS – allowed us to write a single formula, apply a collection of transformations, and run a standard […]

In programming, a namespace is a grouping for procedures, methods, objects and other code that are related to each other.  We can store Excel Lambda functions in namespaces to keep them organized.  The Advanced Formula Environment To create a namespace, you will need the Advanced Formula Environment (AFE).  If you don’t have AFE, you can […]

The gist for the lambdas shown in this post can be found here. When importing this gist, be sure to select “Add formulas to new namespace” and use the name “depn”. The goal There are several methods of calculating depreciation in Excel. The functions SLN (straight line) , DB (declining balance) , DDB (double declining […]

The gist for this lambda function can be found here. The goal The goal in this post is: Create a function to classify data using K-nearest neighbors (KNN) in Excel A solution Here’s a lambda function called KNN: I’ve also included the definition of the FREQ.SIMPLE lambda function. That function produces a two-column frequency table […]

The gist for this lambda function can be found here. The goal The goal in this post is: Create a simple frequency table in Excel with one function A solution Here’s a lambda function called FREQ.SIMPLE: FREQ.SIMPLE has one parameter and as such accepts one argument. data – a single-column array of data. This is […]

The gist for this lambda function can be found here. The goal Here’s a picture of a red velvet cake I baked last week.  I baked this cake from scratch for my son’s birthday. I’m not a baker. But it makes a nice change from working with computers.  Because I’m English, I get most of […]

The gist for this lambda function can be found here. You can download a workbook with example definitions of relative and fixed holidays here. The goal When working with dates in Excel, it’s sometimes useful to have an accurate list of the public holidays in a given year so we can calculate (for example) the […]

The gist for this lambda can be found here. The goal I saw a video from Diarmuid Early in which he discusses the use of the iterative calculation setting in Excel for calculating effective interest rates.  I share a similar view that using that setting can be very dangerous and if possible, it’s best to […]

 The gist for this lambda function can be found here. The goal Inspired by Diarmuid Early‘s YT video “Debt 101” and Brent Allen‘s “Generating an Amortization Schedule” post on his blog, the goal here is Simplify the creation of a series of dates to be used as payment dates for a debt instrument This series […]

The gist for this lambda function can be found here. The goal When we have a dataset with lots of variables (features), we can simplify the modeling process by first trying to determine which variables are correlated with one another.  If two variables are highly correlated, we can consider including only one of them in […]

The gist for this lambda can be found here. The goal There are many reasons for ranking data. Excel provides a few native functions to do so. The two main ones are RANK.AVG and RANK.EQ. Each function will return the integer rank of a number within a list of numbers, sorted either descending or ascending […]

The gist for this lambda function can be found here. You can download an example file here. The goal If we have a table of sales of a product where each row represents one month, then we might want to calculate – for each month – the rolling sum of sales over the most recent […]

This is my attempt to answer a question I have asked myself many times over the last few months: What is a thunk in an Excel lambda function? Background Many of the new dynamic  array functions that create arrays, such as MAKEARRAY, SCAN, REDUCE and so on, will not allow an element of the array […]

The gist for this lambda function can be found here. The goal It’s sometimes useful to be able to group a continuous variable into bins of equal counts such that we can work with that variable as it if were discrete. In mathematics and machine learning applications, this process is sometimes referred to as “discretization”. […]

The gist for this lambda function can be found here. The goal Sometimes we might have numbers in an Excel file formatted like this: I’d like to create a function that those values into a number where each value is at the same scale. A solution I say “a” solution, because I’m sure there are […]

The gist for this set of functions can be found here. The goal This post is not intended to be an instruction on the mathematics or theory of outlier detection. My intention is to demonstrate another way we can use lambda in Excel to simplify a common task. This post will show you how to […]

The gist for this lambda function can be found here. Forecasting in Excel Excel comes with several functions that allow us to quickly produce forecasts on time-series datasets. Data>Forecast Sheet One method you can use is the “Forecast sheet” button which can be found on the “Data” tab of the ribbon in Excel versions 2016 […]

The gist for this lambda function can be found here. I was straining my brain to understand the lambdas posted by user XLambda on the lambda forum at mrexcel.com. This person is really mining the depths of what’s possible with lambda in Excel. I go there frequently when I want to understand how to do […]

Quick start If you want to use this function to get descriptive statistics in Excel with just one formula and without reading all the detail below, you will need to: create a LAMBDA function called RECURSIVEFILTER using this gist create a LAMBDA function called GROUPAGGREGATE using this gist create a LAMBDA function called DESCRIBE using […]

The gist for this lambda function can be found here. A friend of mine challenged me to implement a LAMBDA to calculate the Levenshtein Distance in Excel. As a reminder, the Levenshtein Distance represents the fewest number of insertions, replacements or deletions that are necessary to change one text string into another. For example, given […]

The gist for this lambda function can be found here. Let me cut to the chase. I have some data from Wikipedia for population by country . I’ve also got some data of land area by country, from here. I created a Power Query to merge them. The output looks like this: There are 195 […]

The gist for this lambda function can be found here. Excel’s FILTER function lets you take an array of data (or a table, or a range) and filter it with an “include” array of TRUE/FALSE values, where each row in the include array corresponds to each row in the data array. If the include array […]

The lambda described in this post has been updated with additional features. You can read that post here. You can use the Data Analysis Toolpak to get descriptive statistics in Excel for a variable in your data. First, you need to make sure the analysis toolpak is activated as an Add-in: Then you select “Data […]

The gist for this lambda function can be found here. One-hot encoding. Create as many columns as there are unique values in a variable. Put a 1 in a cell if the column and row represent the same value, otherwise put a zero in the cell. Use these new columns to create ML models. Do […]

The gist for this lambda function can be found here. A common task in Natural Language Processing (NLP) is to tokenize text strings into n-grams. This can be done easily in languages like Python, Scala, R and others. They have very good libraries for performing that kind of task at scale. I wanted to see […]