Lambda functions are now available in Excel (Office 365 only). You can now create your own user-defined functions without resorting to enabling macros and without using any VBA. Lambda functions are built instead from Excel formulas.
I have some of my old running data files in which the times are given in decimal minutes – e.g. 56.75 corresponds to 56 mins and 45 seconds.
The formula shown below can be used to convert the digital time into something much easier to read and understand.
This is not a very large formula, but it is a pain to have to find an example of it somewhere in a workbook to copy it and modify the cell references for re-use elsewhere. This is where the lambda function comes in.
Here is the syntax for an Excel lambda function.
Below I have taken the formula shown above and made it a lambda function. There is only one parameter (which is the time in digital minutes), and I have used x to represent this. All references to cell B3 from above are replaced with x. More complex lambda functions could have many different parameters.
After the lambda function, (B3) is typed on the end. B3 is the cell containing the time we want to convert. This is how we tell Excel which cell is to be passed as the parameter to the lambda function we are testing.
With the lambda function working as it should, now open Name Manager, select New, and then give the function a Name (timeInMinutes) , write a description of what it does in the Comment box to make re-using this function painless in the future, and finally, the lambda function is copied and pasted into the Refers to box. (Note that no cell reference is entered after the lambda function definition.)
The function is now saved, and can be used in exactly the same way as any other built-in Excel function as shown below.
