snth

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

snth

/*
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)

snth

/*
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:

snth

/*
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

snth

# 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}
  )

snth

Hello

Welcome!

snth

import sys

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

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