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

Рет қаралды 2,230

Күн бұрын

An introduction to using the two best functions in modern Excel: LET and LAMBDA.
The problem is Battle 7 from the ‪@FMWC‬ Road to Las Vegas. If you want to give it a go, it's on sale here:
www.fmworldcup.com/product/ta...

Пікірлер: 18

@DimEarly 11 ай бұрын

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: kzfaq.info/get/bejne/iZOYeLtksrGZl2Q.html

@excelthewonder Ай бұрын

Excel is on Fire😮

@harryseiders1336 11 ай бұрын

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

@DimEarly 11 ай бұрын

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! : )

@ExcelWizard 11 ай бұрын

13:00 The Final formula is beautiful 😍

@matteo.gerardo 4 ай бұрын

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

@ankurshukla2516 11 ай бұрын

Great Master waiting for more ❤

@DimEarly 11 ай бұрын

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

@vikhattangady 11 ай бұрын

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 11 ай бұрын

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 11 ай бұрын

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 11 ай бұрын

@@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 11 ай бұрын

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).

@ValyGreavu 11 ай бұрын

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

@DimEarly 11 ай бұрын

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). kzfaq.info/get/bejne/mdGHmNaKl9yooIE.html

@DrKazza 9 ай бұрын

@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 9 ай бұрын

I don't think I'll have time to for the next while, but try this one out: kzfaq.info/get/bejne/apumaLGZtc7ToJc.html 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 KZfaq) recommend it, and he probably knows those functions as well as anyone!

@DrKazza 9 ай бұрын

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