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 / excel-lambda-SUMPRODUCT2.txt
Created April 30, 2024 17:14
This Excel LAMBDA functions mimics SUMPRODUCT but allows a single 2D array as its only argument
IFOMITTED = LAMBDA(arg,then,IF(ISOMITTED(arg),then,arg));
SUMPRODUCT2 = LAMBDA(array, [axis],
ncalm / excel-lambda-list.allpairs.txt
Created April 26, 2024 12:41
This Excel LAMBDA function implements the functionality of F#'s List.allPairs function for creating a cross-join between two lists (vectors)
LIST.ALLPAIRS = LAMBDA(list1, list2,
list1Col, TOCOL(list1),
list2Col, TOCOL(list2),
list1length, ROWS(list1Col),
list2length, ROWS(list2Col),
resultRows, SEQUENCE(list1length * list2length, 1),
rowIndex1, CEILING(resultRows / list2length, 1),
rowIndex2, MOD(resultRows - 1, list2length) + 1,
HSTACK(INDEX(list1Col, rowIndex1), INDEX(list2Col, rowIndex2))
ncalm / excel-lambda-SPREAD.txt
Last active April 16, 2024 23:54
This Excel LAMBDA function mimics argument unpacking for arbitrary functions of up to 5 parameters
// Spread an array of arguments across the parameters of a function of up to five parameters
SPREAD = LAMBDA(function, LAMBDA(arg_array,
arg_vector, TOCOL(arg_array),
function(INDEX(arg_vector,1), INDEX(arg_vector, 2)),
function(INDEX(arg_vector,1), INDEX(arg_vector, 2), INDEX(arg_vector, 3)),
ncalm / excel-lambda-PIPE.txt
Created April 15, 2024 23:29
Excel LAMBDA examples of function chaining
IFOMITTED = LAMBDA(arg, then, IF(ISOMITTED(arg), then, arg));
// Functions for common mathematical operators
MULTIPLY = LAMBDA(x, y, x*y);
ADD = LAMBDA(x, y, x+y);
SUBTRACT = LAMBDA(x, y, x-y);
DIVIDE = LAMBDA(x, y, x/y);
// Apply a series of functions to an array
PIPE =LAMBDA(array, functions, operator, [init],
ncalm / excel-lambda-BINARYSEARCH.txt
Created April 4, 2024 17:09
This Excel LAMBDA function enables simulation and visualization of the binary search algorithm to find a value in a sorted array.
IFOMITTED = LAMBDA(arg, then, IF(ISOMITTED(arg), then, arg));
BINARYSEARCH = LAMBDA(search_for, array, [stop], [iter], [left_index], [right_index],
_iter, IFOMITTED(iter, 1),
_stop, IFOMITTED(stop, ROWS(array)+1),
_left_index, IFOMITTED(left_index, 1),
_right_index, IFOMITTED(right_index, ROWS(array)),
_seq, SEQUENCE(ROWS(array)),
ncalm / excel-lambda-DICECOEFF.txt
Created April 2, 2024 20:16
This Excel LAMBDA function calculates the Sørensen–Dice coefficient between two text strings
GETBIGRAMS - Get the bigrams of a text string
Inputs: 1. str - a text string
Returns: A vertical array of the bigrams of the text string
ncalm / office-scripts-protection.ts
Last active March 22, 2024 13:32
This gist demonstrates how to set protection options in Office Scripts for Excel
function main(workbook: ExcelScript.Workbook) {
const activeSheet = workbook.getActiveWorksheet()
// Returns an object representing the worksheet's protection
const activeSheetProtection = activeSheet.getProtection()
// Protect the sheet according to preferred options (password as 2nd arg is optional)
// This line uses the ternary operator. The protect call is only used if the getProtected() call returns false
ncalm / create-format-chart.ts
Last active March 9, 2024 21:33
This Office Scripts function creates and formats a chart - for exploration and learning
* Create and format a chart from some AdventureWorksDW2019 data
* The table has these columns: Year, Month, SalesAmount
* The worksheet must include a named cell called 'referenceLineValue'. This value is used to draw the line on the chart.
function main(workbook: ExcelScript.Workbook) {
let selectedSheet = workbook.getActiveWorksheet();
ncalm / powerquery-TextSplit.m
Created February 7, 2024 01:09
This code demonstrates one way to parse a string of fields into multiple rows and columns
Source = "APS Deposit 04/01/2022 $5,174.27 APS ACH Deposit 04/04/2022 $65,186.66",
Split = Text.Split(Source, " "),
Typed = List.Transform(Split,
each try Number.From(_) otherwise
try Date.From(_, "en-gb") otherwise _ ),
Accumulate = List.Accumulate({0..List.Count(Typed)-1}, "",
(a, b) =>
c = Typed{b},
ncalm / excel-lambda-two_arg.txt
Last active January 15, 2024 18:38
Some simple functions for pairwise lifting of VBA UDFs
two_arg = LAMBDA(function,
LAMBDA(x, y,
function(x, y)