LET - Names in Formulas - Generally Available

AlastairL Thanks for your valuable comments.

Actually, my original comments was not on capabilities of Lambda function but on Let function ( may be my comments was a bit misleading). I really don't know how the Let function is implemented and I don't understand how a name reassignment is going to impact internal implementation. I know a Referential Transparency purist will argue against reassignment of variable. But I thought reassignment would make the name to refer new value and there is no way to modify previous value. So I thought that a reassignment of a local name is not going to leave any side effect nor does it impact the purity of the function.( sorry I didn't get a chance to read through the documentation referred by you).But my understanding is that if the only purpose of let function is to enhance code readability it is good that it restricts reassignment of name. Anyway, I can get my job done by assigning to new name instead of reassignment.

Regarding Lambda function I absolutely agree that it enforces all principles of functional programming of which two are my favourite-function as param as well as return value and recursion..Thanks

sanjibdutta Excel is a functional programming application. It uses what is known as Referential Transparency in FP which is really important in Lambda Calculus which Lambda function is based on. There's no way Simon Peyton Jones et al who brought Lambda to Excel would allow you to do imperative programming type things inside a Lambda function, that would break everything Lambda is it's based on.

There's some good resources on Referential Transparency online if you want to look. Try the Haskell language documentation for a start. TLDR: RT allows us to reason about our code like mathematical equations and also how it prevents entire classes of coding bugs from being created (in imperative programming, something as simple as an accidental coding mistake which increments a variable it shouldn't could erase the database, fire the missiles or whatever…) 

PeterBartholomew1

Yes Peter, I agree that delaration of numrous names would not clutter global namespace. But only thing I was wondering if indulgance of adhoc number of names would ultimately cost the exeution environmrnt for memory allocation, though only for the life time of the function. My concern may be completely invalid. Actual execution runtime must have optimized memory usage.

I like your systematic naming style..Thanks

I suspect it will be a while before any widely accepted good practice is defined.  I wouldn't worry too much about defining sequences of variables within LET since they are strictly local and do not clutter up the workbook namespace.  I have adopted a number of practices that are not generally accepted but which work for me.  Where the formulas are of a mathematical nature, I tend to use elements of the Greek alphabet together with subscripts, an example of which is given below   

= LAMBDA(D,
    LAMBDA(xᵣ, tᵣ,
      LET(
        δx₁,  δt * D(xᵣ, tᵣ),
        δx₂,  δt * D(xᵣ + δx₁ / 2, tᵣ + δt / 2),
        δx₃,  δt * D(xᵣ + δx₂ / 2, tᵣ + δt / 2),
        δx₄,  δt * D(xᵣ + δx₃, tᵣ + δt),
        xᵣ₊₁, xᵣ + (δx₁ + 2 * δx₂ + 2 * δx₃ + δx₄) / 6,
        xᵣ₊₁
      )
    )
  )

 The steps δx₁,  δx₂, δx₃, δx₄ are successive refinements of a function x(t) corresponding to a step δt.  An advantage of using the subscript characters is that there is no risk of clashing with one of the 17 billion pre-defined cell references.

For business objects my names tend to be much longer such as 'openingAccountsReceivable'.  It is readability rather than conciseness that I value.

@Peter Bartholomew @mtarler  Thanks for your thoughtfull comments.

Actually, I didn't ask this capability in the context of LAMBDA or for loop. As a general exploration of the LET function (I am really new to almost all Office365 new excel functions) I was trying to reassign a name (treating it as variable) that was supposed to be final evaluation/calculation. Instead of creating many names to hold intermediate values I was trying to modify same name that was finally to be returned to reduce number of names. Step by step multiple reassignment(modification) was required as a result of parsing some moderately complex text string. And that's how I was stuck as LET does not allow reassignment of same name. My case was very specific use case and I know I could avoid reassignment with a different approach. But somehow I still wonder if for many other use cases we may encounter such requirement. Having said that I do agree that using same name for assignment of different logical value just to minimize number of names is truly a bad practice and may reduce code readability. I guess, to guard against such bad practice, probably, team has restricted this reassignment capability. Thanks 

mtarler sanjibdutta 

Hi Matt, my understanding of functional programming is that to reassign a value is in violation of its underlying principles and would reintroduce the potential for undesirable side effects that the paradigm was designed to eliminate.  The closest thing that is allowed is to use a recursive call to create a new set of variables, but the originals still have their values unaltered.

Not that I would consider myself in anyway an expert in such matters, @Lori_m took time to educate me and provide references to the Lambda calculus just a while before it appeared in Excel.

PeterBartholomew1  I think the idea is 

=LET(x, 5, y, 6, x, x+y, ...)

so you initialize x as 5 and then do some stuff and then update x to be a new value.  Classical programming would do this in loops and such but not sure when or where it would be of most value here.  I know they mentioned with LAMBDAs and helper functions are sort of like loops but still not sure how it would play in the mix.

sanjibdutta 

I don't think I understand your requirement.  You can assign a value to a LET variable but it will not be the same variable even if it has the same name.

= LAMBDA(x,
    LET(x, 5, x^2)
  )(4)

For example, the above formula returns 25, not 16.

To support variable manipulation in LAMDA function like natural program flow, I would like to be able to reassign values to same name in LET function. It also encourages less number of names and easy tracking variables. Currently it is not supported...Thanks

orthogonal to topic of this post: 

SI dictates that there’s always a space between the number and the unit of measure. including for n °C and n °F. 

its really common to see the space between the number and the unit (°C) omitted in writing around climate change with all the all commentary in mainstream media about 1.5 °C and 2.0 °C “thresholds” for tipping points etc etc. but it’s bad grammar to omit the space. 

see the General Rules section of this his page. 

https://en.m.wikipedia.org/wiki/International_System_of_Units#

ps

anks to Simon Petyon Jones and all those at MS who work on bring FP to Excel. it desperately needed it and is one reason even non-coders are turned of Excel formulae they very quickly become ugly as sin and as debuggable as nonsense. for me as a novice Haskell coder this is an invitation to give Excel another chance for serious work.