Skip to content

Instantly share code, notes, and snippets.

View ncalm's full-sized avatar
💭
hitting computer with hammer

Owen Price ncalm

💭
hitting computer with hammer
View GitHub Profile
@ncalm
ncalm / excel-lambda-NGRAMS.txt
Last active June 15, 2023 09:43
This Excel LAMBDA function converts a text string into n-grams
/*
NGRAMS
Converts a text string to n-grams
Inputs:
- text (string): the text string to convert
- n (int): the number of elements to output in each n-gram
- strict (bool): whether to output incomplete n-grams at the end of the array
@ncalm
ncalm / excel-lambda-ONEHOT.txt
Last active June 15, 2023 09:43
This Excel LAMBDA function one-hot encodes a categorical variable
/*
ONEHOT
One-hot encodes a catagorical variable
Inputs
- rng: a single-column array or range of categorical data (usually text)
Returns
For each unique value v in rng, a new column with header rng.header & "_" & v
@ncalm
ncalm / excel-lambda-LEV.txt
Last active January 5, 2024 08:13
This Excel LAMBDA function calculates the Levenshtein distance between two strings
/*
LEV
Calculates the Levenshtein distance between two strings
Inputs
- a: a string to compare with b
- b: a string to compare with a
- [ii]: the [ii]th position in string a
- [jj]: the [jj]th position in string b
@ncalm
ncalm / excel-lambda-RECURSIVEFILTER.txt
Last active June 15, 2023 09:43
This Excel LAMBDA function recursively applies filter criteria to an array or range
/*
RECURSIVEFILTER
Filters an array or range recursively using a list of columns and criteria to apply
Inputs
- dat: the array or range of data to filter
- cols: Either a one-dimensional horizontal array of column indices representing
columns in dat. e.g. {1,2} means "filter columns 1 and 2"
OR
@ncalm
ncalm / excel-lambda-GROUPAGGREGATE.txt
Last active February 17, 2024 12:13
This Excel LAMBDA function creates summary tables similar to SQL GROUP BY queries
/*
GROUPAGGREGATE
Creates summary tables of data similar to SQL's GROUP BY queries
Inputs
- dat: a range or array of data with at least two columns,
one of which's control value must be "group" and one not "group"
- control: a single-row array, where COLUMNS(control)=COLUMNS(dat), of values from this list:
group - the values in this column will be output as row headers
@ncalm
ncalm / excel-lambda-DESCRIBE.txt
Created April 14, 2022 23:12
This Excel LAMBDA function creates a table of summary statistics for an array of data
/*
DESCRIBE
Creates tables of statistics similar to the Analysis Toolpak Add-in "Summary statistics" feature.
Including support for text columns and additional statistics for numeric columns.
Inputs
- data: a range or array of data with at least one column
- has_header: TRUE if data includes a header row, FALSE otherwise
@ncalm
ncalm / excel-lambda-GROWTHFROMOFFSET.txt
Created April 16, 2022 17:15
This Excel lambda function calculates the growth that a current month's value represents when compared to an arbitrary number of months prior to the current month
/*
GROWTHFROMOFFSET
Calculates the growth that a current month represents when compared
to the same measure an arbitrary number of months ago
Inputs:
- month_col: a Table column, a cell range (using absolute references) or an array containing the months formatted as dates
- value_col : a Table column, a cell range (using absolute references) or an array containing the values
- month_offset : the number of months prior to the month on the current row that you want to compare with
@ncalm
ncalm / excel-lambda-FORECAST.ETS.COMPARE.txt
Last active June 15, 2023 09:42
This Excel lambda function creates an array of actuals vs forecast for quality control of time series data
/*
FORECAST.ETS.COMPARE
Creates an array enabling comparison of actuals vs forecasted data
Inputs:
Required:
- data - this is a two-column range or array of data where one of the columns contains dates and one of the columns contains the values to be used as the basis for the forecast
- dates_in_column - is an integer, either 1 or 2, telling the function which of the two selected columns in data contains the dates
- values_in_column - is an integer, either 1 or 2, telling the function which of the two selected columns in data contains the values to be used as the basis for the forecast
@ncalm
ncalm / excel-lambda-ISLEAPYEAR.txt
Last active June 15, 2023 09:42
This Excel lambda function returns TRUE for a leap year and FALSE for all other years
/*
ISLEAPYEAR
Calculates whether a year is a leap year
Inputs:
- yr is the year you want to test
Returns:
TRUE if the year is a leap year, FALSE otherwise
@ncalm
ncalm / excel-lambda-OUTLIERS.txt
Created April 23, 2022 13:35
This collection of Excel lambda functions provides functionality to quickly apply the standard deviation test on a series of transformations to a continuous variable
/*
OUTLIERS
These lambda functions allow us to quickly apply the standard deviation test to a series of transformed variables
Included in this file:
OUTLIER.THRESHOLDS - for calculating outlier thresholds using a standard deviation test
inputs: