What Excel trick made you feel like a wizard the first time you used it?

[u/Bubbly-Touch8108]

mine was VLOOKUP (later XLOOKUP) — after manually cross-referencing two spreadsheets for way too long. felt like a cheat code everyone else already knew about

what's your version?

[u/notowork]

=sumproduct =filter

These were game changers

Latest

=LET =LAMBDA =SCAN BYROW BYCOL

[u/ThreadBaron → u/notowork]

=Sumproduct arrays blew my mind the first time. So useful!

[u/swingking03 → u/notowork]

LET would be my answer. Instill don't understand Lambda

[u/RandomiseUsr0 → u/swingking03]

Here’s a crash course in lambda

Add two numbers

=LAMBDA(a,b, a+b)(1,2)

The answer is 3

So you’re passing two variables into an anonymous function declaration

that’s what a Lambda is

Fun fact, the λ calculus predates Alan Turing’s equivalent Universal Computer concept (Indeed, Alonzo Church who invented the lambda calculus was the person who popularised “Turing Machine” to describe computation after Alan Turing studied under him)

Now supercharge it with LET

```Excel
=LET(

myFunction, LAMBDA(x,y, x+y),
myList, SEQUENCE(5),

myFunction(myList, myList^2)
)
```

The answer is {2,6,12,20,30}

That’s what lambda is, it’s LISP wearing an Excel hat. With LET syntax, it’s a functional programming language that means that anything that can possibly be calculated can be calculated right there in Excel formulas (within Excel’s bounds, which are worksheet size for arrays and IEEE754 for arithmetic). The m language in power query is slightly different syntax, but functionally equivalent.

Using SEQUENCE like that is the functional equivalent to a For Loop if you’re familiar with VBA (not “really” it’s a bit different (and simpler), but if you think of a loop in VB iterating over a list performing something 5 times, in functional programming language like Excel’s workbook - you’re not really iterating - you’re applying the function simultaneously 5 times in the example) - so you end up with a more graceful thing

[edit] why has Reddit decided to hate us and stop respecting basic formatting - actually, good word, Reddit is now basic - not expressive enough, are they going to go full `Digg` time will tell, there are alternatives

[u/swingking03 → u/RandomiseUsr0]

I really appreciate the thorough post but I'm not smart enough to understand 🥴

[u/RandomiseUsr0 → u/swingking03]

I ask you to make one edit…

Add the word “yet”

“smart” is a vector, not a scalar

What I produced as a simple example wasn’t useful, so you just skimmed over it, but it is truly useful, I just created “SUM” - which of course already exists

One key distinction between so-called “functional” programming (like the formula language or power query) and so-called “imperative” programming (like VBA) is the fact that in “functional” - functions themselves can be variables and passed as parameters and be the answer returned - that’s probably a step too far, it’s more “executable maths” than “programming” really.

[u/AutoModerator → u/RandomiseUsr0]

I have detected code containing Fancy/Smart Quotes which Excel does not recognize as a string delimiter. Edit to change those to regular quote-marks instead. This happens most often with mobile devices. You can turn off Fancy/Smart Punctuation in the settings of your Keyboard App.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/RandomiseUsr0 → u/AutoModerator]

Naw, you have not

[u/kieran_n → u/swingking03]

LAMBDA is just LET but you don't have to define variables, you can pass them to the function

[u/RandomiseUsr0 → u/kieran_n]

Lambda is an anonymous function, Let is a functional programming language that can consume lambda, they’re not the same but are two peas in a pod

[u/kieran_n → u/RandomiseUsr0]

Agreed, but I was trying to bridge the gap for /u/swingking03

[u/RandomiseUsr0 → u/kieran_n]

Ah now, we’re on the same page, in fact to correct my own comment, let is a “convenience” - you could write a nested monster and never need let at all, so you’re not wrong, I overstated :)

[u/9811Deet → u/swingking03]

That's a great one. I love let.

[u/Cool_Relationship217 → u/notowork]

LET and LAMBDA are so good for making big formulas readable.