Mar 26 2021 03:31 AM
Mar 26 2021 03:31 AM
The past of Excel lies very much in the tradition of low-tech, ad-hoc solutions that allow end-users to interact with their 'numbers' with little or no knowledge or interest in programming.
Now we are faced with the announcement of Lambda functions and claims that Excel is 'Turing-complete'. I have only a hazy idea what that implies, but I take it as meaning that 'anything that can be calculated in any other functional or imperative programming language, can now be calculated in Excel. I am not sure that is strictly true given current limits on the depth of recursion of Lambda functions, but the intention is clear.
In the video, I have used nested Lambda functions to access non-normalised data as if it were a multi-dimensional array [so next time I look at a question on the forum and think 'frankly, I wouldn't start from here', I have a way forward].
Note: The reference to @Bill Jelen in the attached file comes from the fact that when I scanned my Excel folders for a suitable data set I lighted upon one of Bill's formula challenges. Most of the solutions Bill received at the time were built using Power Query. The Lambda functions described here offer a further solution almost by accident.
Points that might prove worthy of further discussion could include:
I will be very interested to see how others view the new developments and whether they will be coming along for the ride. Maybe you look elsewhere when it comes to 'serious' programming, or maybe you see the future as AI (artificial intelligence) providing functionality without involving the end user?
Mar 26 2021 09:43 AM
@Peter BartholomewNice construction, will experiment with them, but they certainly look promising! To muse on your questions, see my responses below
These may also be the musings of someone who doesn't know as much as he thinks he does.
Mar 26 2021 11:25 AM
@lori_m has just sent me a link to
If you must make a choice, listen to them rather than me! After all, I now know that my recursive Lambdas have been using 'Tail Recursion'. My learning by trial and error takes me so far, but it will not necessarily embrace the standard terminology.
On thing that will need to be sorted, is that Andy and Simon probably shouldn't use the term MVP to refer to 'Minimum Viable Product' in the MS Excel context!
Mar 26 2021 02:49 PM
Finance is something of a closed book to me; I might even do better with general relativity! I was still shocked by the assumptions of the FAST standard:
FAST 2.02-01 Construct all calculations in a separate calculation block
FAST 4.03-01 Do not use Excel Names
FAST 3.03-09 Do not construct array formulas
They have key recommendations that I trap as errors. Their objective was always to use the most primitive operations that could give the result. Exactly the opposite of your abstract data types that are constructed to capture the properties and behaviours of the objects of interest to you.
Mar 26 2021 03:30 PM
Mar 26 2021 04:00 PM
Mar 27 2021 05:06 AM
Even if I'm for decades in IT business I'm not a professional programmer, more street developer, and afraid can't support such discussion, just have not enough skills. However, few comments from practical point of view.
Functional programming in general and lambdas in particular have a long history and have their own niche, and, as everything in this world, they have their strong and weak points. Among weak ones are poor maintainability and complicated re-use. From this point of view I'm bit disappointed by Andy Gordon comments that he has no idea how lambdas will bi distributed and supported, perhaps through Twitter. LAMBDA in Excel needs to have more support, these are additional functionality like MAP(), more rich IDE compare to current formula bar and Name Manger and perhaps kind of framework. Without that it will be quite limited usage.
Declaring of Minimum Viable Product (MVP), which was quite popular few years ago, applied to LAMBDA is a bit formal. MVP is a great approach, but it's not a golden bullet which solves everything and need it's own set of tools to support.
@Liam_Bastick knows much more about financial modeling in particular and about Excel in general, perhaps he comments. From my point of view the core of FAST is to ensure reliability and maintainability taking into account current status of Excel. From that point of view it's quite reasonable.
Despite of some scepticism I consider introduction of lamdas in Excel as a great step forward. With some further support it will have it's own niche, how wide it depends on supporting tools. But IMHO, that doesn't mean LAMBDA or nothing.
Mar 27 2021 03:07 PM
I think you are under-selling the depth of your understanding! Like you, though, I have never been a professional programmer. I did develop and prototype computer methods for the design of aircraft, but the final coding was done under contract. I missed the early days of spreadsheets because I was using VAX and Prime computers.
When I did get involved, I was not especially impressed with the code developed by user interaction on the worksheet, nor by macro recorder (highly impressive mechanisms for developing bad code?). What I look for in code is readability, relating data objects to their business context, and I want the process intent to be clearly expressed. By 2010, my workbooks were dominated by Names and Array formulas. I had discovered that array formulas worked when committed via Name Manager but had no way of telling whether that was the intended behaviour or some accident. When FAST came out (in 2015?), I took it to be a claim that its proposed methods represented 'best practice' and other approaches were to be deprecated. After a lot of discussion with Andrew Berkley of F1F9, we agreed to differ on the detail, with me accepting that their need was for shared practices that could be deployed by end-users.
Dynamic arrays, LET and now LAMBDA make my life so much easier, building on things I was doing anyway but without the pain of CSE and allowing me to use local names far more freely. One case that I do argue is that 'simplicity' is not achieved by adopting to most primitive methods; it requires the most appropriate. What I try to avoid it moving from being 'thought provoking' to being just plain annoying; that is very difficult to judge.