Tobias Brandt snth

Hello

Welcome!

Recursion in Excel

In my recent post about TRIMRANGE (https://www.linkedin.com/posts/activity-7267473059383582720-mhJG) I mentioned that I would follow up with more to say about recursion.

Recursion is tricky in any context but probably even more so in Excel as it is not natively supported. However #Excel does support first-class functions and with that we can use a trick first discovered by Alan Turing which also goes by the scare name of "Theta Combinator", the lesser known but equally if not more useful cousin of the "Y Combinator" (if those terms don't scare you then have a look at https://en.wikipedia.org/wiki/Fixed-point_combinator). Incidentally the Y Combinator goes back to Haskell Curry after which the concept of "Currying" is named - a fascinating subject in its own right but a topic for another day.

In my TRIMRANGE implementation I defined a "_loop" LAMBDA which mimics a while loop in other languages. Now Excel Formulas are a "functional programming language" which limits what you can do with

	import sys

	def wait_for_enter():
	input("Press Enter to continue: ")

	def create_ssh_keys(context):
	"""Run:
	ssh-keygen -t rsa -f ~/{username}
	"""
	pass

	# Try this for yourself at: https://prql-lang.org/playground/

	let arr_agg = func x -> s"array_agg({x})"

	let jazzTracks = (
	from t=tracks
	join g=genres (==genre_id && g.name=="Jazz")
	select {t.*, genre=g.name}
	)

	/*
	TRIMRANGE (https://support.microsoft.com/en-us/office/trimrange-function-d7812248-3bc5-4c6b-901c-1afa9564f999)

	Excludes empty rows and/or columns from the outer edges of a range or array
	by scanning inward until finding non-blank cells.

	Inputs:
	- range: The range or array to be trimmed
	- trim_rows (optional, default=3): Controls row trimming
	0 = Keep all rows

	/*
	XMAP

	Applies a function to each row, column, or element of an array, with configurable mapping dimension.
	Like MAP() but with added flexibility for handling multi-dimensional arrays.

	Inputs:
	- array: The input array to map over
	- function: The function to apply to each row/column/element
	- dimension (optional): Controls how the function is applied:

	/*
	AND⚡ (Short-circuiting AND)

	A performance-optimized version of AND that short-circuits evaluation using CHOOSE,
	stopping as soon as it encounters FALSE.

	Inputs:
	- logical1: First logical test (required)
	- logical2-logical9: Additional logical tests (optional)