How to use the Excel SCAN function (for running totals and more)

[@nemoyatpeace]

Woot, I learned a new function! Can't wait to compete Saturday!

[@DimEarly]

Same! : )

[@HussainAliNasser]

So glad you made this video - I know basic SCAN/REDUCE (cumulative aggregation) but keen on using it like Bo and yourself in more complex ways

[@ExcelHechoFacil]

Gracias, es un punto de vista muy poderoso el que das en este video. Sorry, My English is not good.

[@DimEarly]

Si puedes entender mis vídeos, tu español es mucho mejor que mi inglés!

[@ExcelHechoFacil]

@@DimEarly Thanks.

[@nytsiory]

Now that you tackle lambda helpers, would really love to have your explanation of the MAP function.

[@DimEarly]

🤔 I just might do that… when I can find the time!

[@johnborg5419]

If only I have only 1/3 of your knowledge in excel!!!! :) :)

[@DimEarly]

Haha - if you’ve watched this, you know more than I knew about SCAN 18 months ago. Keep learning!

[@brianxyz]

SCAN seems very similar to LIST.ACCUMULATE in Power Query.

[@DimEarly]

Yes, I think the usage is pretty much the same 👍🏻

[@HadynWiseman]

Does this negate the need for recursive lambdas or are there things you can do with a recursive lambda which can't be done here?

[@DimEarly]

I would say not completely, but SCAN + REDUCE covers a LOT of cases I would have used recursion for before. I can only think of two cases I would use a recursive LAMBDA for now: * one would be where I don’t know in advance how many steps will be needed, so a SCAN / REDUCE with a fixed number of steps wouldn’t work unless you manually tune the number of steps * the other would be if you’re thinking seriously about computational efficiency. Peter Bartholomew and Owen Price did some work on this a while back, and found at least some cases where they could do a set of calculations a chunk faster with a carefully crafted recursive LAMBDA than with SCAN / REDUCE. I talk a bit more about the distinction on the first one at the start of this video (most of the technical part of that video is already outdated, as I did it before I knew LAMBDA helpers, but the theory at the start is still good): Write Excel formulas like a programmer kzbin.info/www/bejne/qWLbn3ZtebeSn80

[@HadynWiseman]

@@DimEarly Very interesting, I find the logic of recursive lambda a bit harder to get my head around than scan and reduce for now so will start with those. I have only used reduce once so far so more practice needed. I will check out the other video now, every night tends to be a good hour going through your older videos to learn as much as I can!

[@DimEarly]

Wow! That’s serious dedication - I’m glad you’re finding them useful!

[@spilledgraphics]

Nice haircut Dim! 😁

[@DimEarly]

You know me, always stylish… 😂

[@sledgehammer-productions]

What does your training consist of? I'm just trying to sleep well, don't get anxious and hope for the best. I've already reached my goal of qualifying. I'm glad that I'm done at 10:15 AM my time 😀 Break a formula/lambda for your games!

[@DimEarly]

Training is mostly just doing some old cases… although your approach sounds pretty sensible too. Break a formula! : )

[@Mishkafofer]

Where can I find xl files from the competition?

[@DimEarly]

They sell them on the FMWC website (or you get them for free if you take part). This is the past cases store - there’s a section for esports, and the ‘seasons’ are the financial modeling cases. fmworldcup.com/product-category/case-studies/ There’s also an option to sort by price low to high, and you’ll find a half dozen or so that are free to download.