In the latest releases of Excel, there are new functions that are quite powerful. This is a place for people to talk about them, ask questions about them, and extol their virtues.
Here’s one:
=lambda(r, r * r * pi() )(11)
These are more straigtforward than using a combination of Mid and Find to extract segments from longer strings.
Sorry, what is this function doing? What is the “11” for? “This one goes to 11”?
The lambda function returns a function, and its syntax is something like LAMBDA([arbitrary number of arguments, each of which is an argument of the function being returned], [calculation using the arguments]).
So lambda(r, r/pi()) returns a function with one argument r that calculates the value of r/pi(). Lambda() doesn’t return a number, it returns a function, so like any other function, you must give it arguments in parentheses—that’s what the (11) is. So 11 is passed to the function returned by the lambda function as argument r.
I’ve played around with them in Google Sheets, though now I notice I finally have them on Excel too. I like that they can be used to make custom functions without needing a macro book—for example, in the name manager set the name “myFunction” to “=LAMBDA(x,y,x+y)” and then you can use myFunction(1,2) to get 3. I also like that they support recursive functions—for example, in the name manager set “myFunction” to “=LAMBDA(x,x/2+if(x/2>0.0001,myFunction(x/2),0))” and then you can use myFunction(1)=approximately 1…since it just sums up half the number you give it, plus half that number, etc. I’m sure someone could think of a more interesting recursive function that does more than return approximately the number you give it.
TEXTBEFORE, TEXTAFTER, TEXTSPLIT are very useful when trying to parse text
The word lambda defines a custom function.
The custom function can have any number of parameters, then after the list of parameters is the functions. In this case, there is but a single parameter r that is then used in the function. The formula follows is rrpi() which is the area of a circle with radius r. The (11) that follows in parenthesis is passed to the function as the value of variable r, so the results is the area of a circle with radius 11.
Thank you, DP, and to AX as well.
I assume this would be used more for complicated formulas? cuz that seems like way too much work to determine the area of a circle when simpler Excel solutions exist.
Can the “11” refer to a cell instead of an actual number?
You betcha
You betcha
Cool!
Just like an actual function that already exists in Excel.
THe Let function is another new excel construct.
The parameters are input in the beginning in pairs, with the alternating parameter name then what the parameter refers to. Then the last thing in the formula is the formula.
example: Let(a, m1001,b,m1002,c,m1003,d,max(1,m1004),e,a1:a10, f,b1:b10,a^3+b^2+c^1+d+sumproduct(e,f))
This is especially helpful for long formulas that see some ranges or references that are used multiple times
The LET function I’ve actually been using for work. Even if a colleague isn’t familiar with the function, I think it’s way more readable than having a long calculation two or three times copy/pasted in a formula, if the calculation needs to be referenced multiple times. (Of course a helper cell could be used, but sometimes that’s not desirable.)