MS Excel: Functions are first-class objects

What makes a function a first-class object?

This is almost certainly an advanced discussion. And, we will start with a short introduction to what makes a first-class object. As the phrase implies, a language (or a programming platform) considers a function as a first-class object if it treats a function on par with other objects such as an array or a list. This means

• We can assign a function to a variable.
• We can pass a function as an argument to a function.
• We can return a function as a result from a function.

The 2 sections below address how Excel treats functions as first-class objects and how Python does the same.

Excel's functions, particularly the LAMBDA function

Modern Excel's functions are first-class objects. The LAMBDA function in particular, with the help of the LET function, satisfies all of the above requirements.

• We can name a LAMBDA using LET and then use that named function.

=LET(x,LAMBDA(n,SUM(n)),
? ? x(SEQUENCE(9))
? ? )        

• We can also name a function using the Name Manager. This function, or formula, is now available within the worksheet or workbook depending on the scope of the name. For more, see Announcing LAMBDA.
• We can pass a function as an argument to a LAMBDA and have it call that function. The example below defines a function x that has 2 parameters, f and n. x calls f passing it n as an argument. Given another named LAMBDA function, _sum, we can call x passing it the function _sum and an array of integers 1..9. The result, as one would expect is the sum of the nine integers or 45.

=LET(x,LAMBDA(f,n,f(n)),
? ? _sum,LAMBDA(n,SUM(n)),
? ? x(_sum,SEQUENCE(9)))        

• We can return a function as the result of an Excel function and call the returned function. In the example below, the IF function returns a reference to either _sum or _mult and we then call the returned function. So, half of the time the formula will return 45 and the other half we'll get 362880

=LET( _sum,LAMBDA(n,SUM(n)),
? ? _mult,LAMBDA(n,PRODUCT(n)),
? ? _chooser, IF(RAND()<0.5,_sum,_mult),
? ? _chooser(SEQUENCE(9)))        

An alternative formulation, which some might find easier to understand, is below:

=LET(_choice,RAND(),_n,SEQUENCE(9),
? ? _caller,LAMBDA(f,n,f(n)),
? ? _sum,LAMBDA(n,SUM(n)),
? ? _mult,LAMBDA(n,PRODUCT(n)),
? ? _caller(IF(_choice<0.5,_sum,_mult),_n))        

Functions as objects in Python

The above treatment of functions in Excel is similar to how other languages such as Python or JavaScript treat functions. In this section, we see how Python treats functions as objects - JavaScript has similar constructs. That means:

• We can assign a function to a variable.

def f(o)
? ? return(sum(o))
g=f
print(f(range(10)))
print(g(range(10))):        

Subsequent examples refer to 2 Python functions. The first sums the values in a list, and the second multiplies the values.

def _sum(l):
? ? return(sum(l))
def mult(l):
? ? from functools import reduce
? ? return(reduce(lambda a,b:a*b,l))        

• We can pass a function as an argument to a function. Below, the function g has 2 parameters, o and l. o is a function and g returns the value returned by o.

def g(o,l):
? ? return(o(l))        

We would use it as

print(g(_sum,range(10)))
print(g(mult, range(1,10)))        

• We can return a function as a result from a function. Using the above 2 functions _sum and mult, in the following example _choose returns each with a 50% probability.

def _choose():
? ? from numpy import random
? ? return(_sum if random.random()<0.5 else mult)        

and we would use it as below. Half of the time we will see 45, the other half 362880

print(_choose()(range(1,10)))        

Essentially, there is no difference between how we treat an object like a list and a function.

Summary

With the recent enhancements to Excel's functions, Excel is a first-class programming platform - it is Turing complete. What that means is that we can do any computation in Excel by itself! The article explained how functions are now first-class objects in Excel, laying the foundation for more sophisticated analytics involving, say, recursive functions.

Have you explored modern Excel's functions? Share your ideas and thoughts on how Microsoft has changed Excel with dynamic array formulas, spill arrays, and related functions.

My previous post: Generate unique random integers