ttarchala

ttarchala / Excel-CROSSJOIN

Created March 12, 2024 16:05

Excel-CROSSJOIN

	// CROSSJOIN: Given two tables, returns the Cartesian product of rows from the tables.
	// Accepts 2 x 2-d range; 3rd parameter can skip headers if true (which is default)
	// Example of use:
	// =CROSSJOIN(
	// CROSSJOIN(
	// TrimToData(Q$2:Q$1000),
	// TrimToData(U$2:U$1000),
	// FALSE
	// ),
	// TrimToData(T$2:T$1000),

Last active January 4, 2024 11:33

Excel range finding utility functions: lastDataRowOf1stCol, TrimToData

	// lastDataRowOf1stCol: a utility function to find out in which row data ends
	// Accepts a column or a 2-d range; for the latter, considers the first column only.
	// Robustly considers any data type, handles blanks in the range. If no data, returns 0.
	// Example of use:
	// =lastDataRowOf1stCol(A:A)
	// =lastDataRowof1stCol(B4:C9)

	lastDataRowOf1stCol = LAMBDA(range,
	LET(
	firstCol, TAKE(range, , 1),

ttarchala / Excel-EvaluationTime

Last active January 3, 2024 17:49

Evaluation time of a function

	// based on this Microsoft Tech Community article by Kevin Dean:
	// https://techcommunity.microsoft.com/t5/excel/timing-function-execution-using-lambda/m-p/3920420
	// Measures the evaluation time of a lambda function call passed as argument.
	// To use with builtin functions, or with your own user-defined functions, wrap them in a LAMBDA().
	// =EvaluationTime(LAMBDA(<yourFunction>(<yourArguments>))

	EvaluationTime=LAMBDA(F,
	LET(
	startTime, NOW(),
	result, F(),

ttarchala / Excel-Hash-FNV1a

Last active January 3, 2024 15:12

Excel hash function FNV-1a 32-bit

	// FNV1a: a 32-bit hash function
	// Accepts any input: string, number or bool. Returns 8 hex characters, or 0 if input is empty.
	// example of use:
	// =FNV1a("Hello world")
	// -> yields "594D29C7"
	// =FNV1a(42)
	// -> yields "87E38583"
	//
	// based on this SuperUser answer by Jason Kleban:
	// https://superuser.com/a/1720348/7659