Write Excel formulas like a programmer

[@Harryjackgross]

Great video as always - I didn’t realise until I tried it today that you can pass functions as arguments to lambdas - crazy!

[@DimEarly]

The rabbit hole goes deep on this thing... if you're ready for the strong stuff, check out Jan-Karel Petersie's LAMBDA discussion group - there's whole sections of it I haven't even wrapped my head around yet, but definitely some interesting discussions!

[@naiduvikas202]

Who are you man ...? You are making stunning knowledge videos. Loving from India

[@DimEarly]

Thanks - I'm glad you enjoy them!

[@Reduce_Scan]

Fun topic , I would like a Clip on the same idea, but without using the defined name

[@twalton]

This was amazing. Thank you!

[@DimEarly]

Glad you liked it! : )

[@RenierWessels]

Amazing as always! Great video man.

[@DimEarly]

Thanks Renier!

[@Reduce_Scan]

3:43 How many digits 7 are ... Second Bonus Question: 300 =LET(a,CONCAT(ROW(1:999)),b,LEN(a),SUM(--(MID(a,SEQUENCE(b),1)+0=7))) First Bonus Question: 35 =COUNT(VSTACK(1,SCAN(1,SEQUENCE(34,,2,0),LAMBDA(a,d,a*d))))

[@DimEarly]

Nice formula for the 300! I'm not sure about the second one though - it looks like the logic is circular (i.e. it gives you 35 because your input sequence goes up to 35, but your sequence only goes to 35 because you already know that's the answer).

[@Reduce_Scan]

@@DimEarly Really, I would be grateful if you give me formula to solution

[@DimEarly]

Sorry, I forgot I hadn't done the bonuses in the video (I usually do the whole question, but I was trying to be more 'thematic' on this one). If you're mathematically inclined, you can use =LOG(17179869184,2)+1, because LOG gives you the power you need to raise 2 to to get that number (and you add one because the sequence starts at 2^0=1, not at 2^1=2). If you wanted to do it more from first principles, you could use something like this: =XMATCH(17179869184,SCAN(1/2,SEQUENCE(50),LAMBDA(a,b,2*a))) The SCAN returns the sequence, and then you just match against that. You'd have to manually adjust the 50 argument if it wasn't big enough. Or, if you wanted to be a little cheeky, you could use the Collatz function I wrote in the case, because that basically steps through the same sequence in reverse (it's always even, so it always divides by 2), and counts the steps to get back to 1. So =Collatz(17179869184) would also return 35.

[@Reduce_Scan]

@@DimEarly Thanks for this much knowledge , very happy with LOG Function , Please I test the initial value with blank without space and got same result the Question How give me 1 in first range SCAN(,SEQUENCE(50),LAMBDA(a,b,2*a))

[@peachfreude]

That was enjoyable

[@ExcelWizard]

Absolutely amazing experience to see you build LAMBDA step by step, your thought is really organized and systematic. Fibonacci, You even cover small detail like if N is less than 3 choose t1 or t2. I would miss that as I just go like if N equal 2 then t2. Alternative REDUCE =--RIGHT(REDUCE(TEXTJOIN(" ",,G134:H134),SEQUENCE(J134-2),LAMBDA(a,v,TEXTAFTER(a," ")&" "&RIGHT(SUM(--TEXTSPLIT(a," ")),3))),3) Collatz Good thing to use ISOMITTED but if we don't put N by default N is blank and then blank +1 = 1 so this is my version I skip ISOMITTED. Collatz =LAMBDA(s, [n],IF(s = 1, n + 1, Collatz(IF(MOD(s, 2), s * 3 + 1, s / 2), n + 1))) and with SCAN =XMATCH(1,SCAN(G99,SEQUENCE(,999),LAMBDA(a,v,IF(MOD(a,2),a*3+1,ROUND(a/2,)))))+1 Level5 I nested everything in 1 LAMBDA SeqAvg = LAMBDA(ar,n,if(n,SeqAvg( ROUND((HSTACK(CHOOSECOLS(ar,2),DROP(ar,,-1))+HSTACK(DROP(ar,,1),CHOOSECOLS(ar,-2)))/2,) ,n-1) ,ar))

[@DimEarly]

Very nice! I was just telling someone yesterday that SCAN, MAP, and REDUCE are top of my list of functions I want to learn to use properly - and I feel like I'm off to a good start just by studying your Collatz and Fibonacci functions. And good point about the omitted argument - I hadn't thought about the default value, but that makes it much simpler!

[@ExcelWizard]

Thank you. I learn from you that the first point to create iterate LAMBDA is IF Exit. That makes it easier to write the LAMBDA. 😍😍 One more REDUCE to crack for Level5, have fun 😄 =INDEX(--TEXTSPLIT(REDUCE(TEXTJOIN(" ",,G171:Q171),SEQUENCE(S171),LAMBDA(t,n,LET(ar,TEXTSPLIT(t," "),TEXTJOIN(" ",,ROUND((HSTACK(CHOOSECOLS(ar,2),DROP(ar,,-1))+HSTACK(DROP(ar,,1),CHOOSECOLS(ar,-2)))/2,)))))," "),7)

[@TA_ANTA]

​@@DimEarly Hi guys, great video and alternative solutions discussed here. I'm still following along and learning so apologies in advance if this is a silly question but is there a decision tree logic that you guys apply to determine when to use a recursive lambda over using SCAN etc. like in the Collatz example? Or is it purely a matter of personal preference and whatever comes to mind first?

[@nocturneuh]

Thanks 🙏

[@gsz2023]

Thanks for the great video I learned a lot. Have you ever used DAX in excel?

[@DimEarly]

Only very little - I use Power Query a lot, but Power Pivot not much. Glad to hear you found it helpful!

[@antonyroussel]

Hi! I've been trying to find ways NOT to use VBA, but I haven't been able to find a way to trigger an action (whatever that might be - just say calculate a formula in a cell), based on user interaction without it. Is there a way to perform an action if a user clicks on some kind of button or link etc, without using VBA?

[@DimEarly]

Mostly no, unfortunately. You can use Office Scripts, which is sort-of-kind-of like VBA (with the advantage that it works in Excel online). You can do some very limited actions without either (e.g. use a button as a hyperlink to navigate), but generally event triggers are the realm of VBA / Scripts.

[@antonyroussel]

@@DimEarly Thank you for getting back to me! Really appreciate the details too.

[@LambdaBam]

I tried the Fibonacci formula with and without the mod(x+y,1000), but I'm not getting any different results. Am I missing something?

[@DimEarly]

I think you must be... if you just do the first few values they'll be the same, but they grow bigger than 1000 very quickly, and I think you'll hit Excel's limit on a number size (or at least its limit on the number of digits of accuracy it stores) well before you can get some of the answers if you don't use MOD to keep them smaller as you go along.

[@LambdaBam]

@@DimEarly thanks, I fixed it! I just had to completely rewrite the formula. User error.

[@DimEarly]

@@LambdaBam Haha - happens to us all : )

[@Reduce_Scan]

Level 4 Please test this Formula =RIGHT(DROP(REDUCE(Z20:AA20,SEQUENCE(AB20-2),LAMBDA(a,d,HSTACK(INDEX(a,,2),SUM(a)))),,1),3)+0

[@DimEarly]

Nice! That works for the smaller examples, you'd need to tweak slightly for the ones with more terms, because once the numbers get above 15 digits, it won't keep full precision. If you use MOD to cut down to the last 3 numbers along the way, it works great: =RIGHT(DROP(REDUCE(H141:I141,SEQUENCE(K141-2),LAMBDA(a,d,HSTACK(INDEX(a,,2),MOD(SUM(a),1000)))),,1),3)+0

[@Reduce_Scan]

Level 5 Please test this Formula =INDEX( REDUCE(B19:L19,SEQUENCE(O19),LAMBDA(u,i, LET(v,REDUCE(u,SEQUENCE(,10),LAMBDA(a,d, HSTACK(a,ROUND(SUM(INDEX(a,,d),INDEX(a,,d+2))/2,)))), g,DROP(DROP(DROP(v,,10),,-1),,1),HSTACK(INDEX(v,,2),g,INDEX(v,,10))))),7)

[@DimEarly]

Nice! I haven’t had a chance to test it yet, but the logic looks good!