Just in case modern-day spreadsheets don’t already have enough functions for you, I learned that both Microsoft Excel and Google Sheets have added the ability to define your own custom functions, even without having to learn a new programming language.
If you can express what you want your function to do in terms of standard spreadsheet formulae – and you should usually be able to do so given they’re now apparently Turing complete, even if it may occasionally be painful – then you can save it as a function you and others can use in your spreadsheet directly.
The Google Sheets documentation gives the simple example of a user creating a function called “CONTAINS” that returns true if the the cell it’s applied to contains a given word and false if not. They define CONTAINS as:
=NOT(ISERROR(MATCH(search_query,search_range,0)))
Save that function and then you can write things like =CONTAINS(“test”, A1) throughout your spreadsheet to determine whether cell A1 has content that contains the substring “test”.
Another option is to define the function inline as a lambda function. Both packages provide the LAMBDA() function to do that. The example usage given for Google sheets for a function that 30% of an employee’s salary is as follows:
=LAMBDA(salary, salary*0.3)(C5)
That of course is a lot more verbose and confusing than simply multiplying a cell value by 0.3, but of course if you get into the territory of more complex functions, array formulae and the like you can do much more interesting and complicated things.
In both products this ability to create custom functions is available in both “named” and inline lambda function styles. The documentation for Excel is here, and for Google Sheets look here for the named function style and here for the lambda syntax.
Dabbling with Data 