LET and LAMBDA - getting started with the two best functions in modern Excel

[@DimEarly]

When I recorded this, I went on to tackle level 5 and bonus 3, but then decided to cut that out to keep this video more approachable. But then I decided it was a shame to waste that, so I uploaded a separate (unlisted) 'director's cut' with the bits I left out. You can find it here if you're interested: kzbin.info/www/bejne/iV7LdotpgrBonZI

[@harryseiders1336]

Great video! I always thought that reduce needed the word lambda in the third argument

[@DimEarly]

Technically, it just needs a function that takes two variables. So if the defined name is set up right, you can just drop it right in - it’s very satisfying! : )

[@ankurshukla2516]

Great Master waiting for more ❤

[@DimEarly]

Haha - the summer is always busier in real life, and hence quieter online... but I'll have a few more things coming up soon...

[@ExcelWizard]

13:00 The Final formula is beautiful 😍

[@vikhattangady]

Thanks for the great video. I did not notice how you calculated your offsets depending on whether the ball started with a Red or Blue player. In my case, I set up the offset calc basis a red player, and then had an additional multiplier of -1 for the offsets if the LEFT(player name)="B"

[@NeilFSJM]

If you check the other video from the pinned comment you can see it in there, but basically exactly as you describe (if startpl begins with "R" then 1 otherwise -1 and multiplies by this)

[@vikhattangady]

I saw it in that video for Level 5, but I am looking at the LET formula at 08:06. That formula seems to work for level 2, 3 and 4, but that does not seem to have any such multiplier.

[@NeilFSJM]

@@vikhattangady the LET function he shows doesn't match the LAMBDA in name manager I don't think, when creating it in name manager he says he's already done it and I suspect there were changes made to it which we don't have visibility of> The point of the video was only to show how to make a LET & LAMBDA (and the benefits), not to show every step of the actual solution.

[@DimEarly]

Yes, you're both spot on - I used the same approach as you did for the direction multiple, something like IF(LEFT(startPl)="R",1,-1), but I didn't include that in the LET or cover it in the video. I think that was the only change between the LET and the 'here's one I made earlier' LAMBDA (although I couldn't swear to that).

[@DrKazza]

@DimEarly - any chance you could do a video going through the different use cases for "Reduce" "Scan" and "Map", I'm still confused about when to use each one - Cheers

[@DimEarly]

I don't think I'll have time to for the next while, but try this one out: kzbin.info/www/bejne/ambZZoGehc2ipsU This is Mike Girvin on the LAMBDA function, with a chunk of time on each of the LAMBDA helpers (as well as lots of other stuff). I haven't watched it myself, but I've seen Bo Rydobon (Excel Wizard on KZbin) recommend it, and he probably knows those functions as well as anyone!

[@DrKazza]

@@DimEarly actually that video was a perfect example - thanks loads.

[@excelthewonder]

Excel is on Fire😮

[@matteo.gerardo]

could you make a video applying this to a financial model?

[@ValyGreavu]

I am very currious about your way to solve bonus 2 question of this case. Thank you!

[@DimEarly]

Check out this video by @harryseiders1336 - he covers the full question (and a totally different approach from mine too, so you might find the bits that I did cover interesting too). kzbin.info/www/bejne/mZy6lqaPZ9t3pq8