Excel, lambdas and thunks with calculation time
This is my attempt to understand how thunks work with Excel lambdas. I could be wrong somewhere, welcome to correct.
First about thunks in general. If you are not familiar with them, starting point could be this wiki page. In brief that is piece of code which is evaluated when another function calls it.
To play with thunk let take as the basis Timer function which is provided in samples for Advanced Formula Environment, more exactly in this library.
Let generate test function in AFE sample workspace for which we would like to get calculation time
n = 100000 m = 40; test= LAMBDA( MAKEARRAY( sample.n, sample.m, lambda(r,c, SQRT(r^c) )) );
Function generates some 2D array. Not to enter into the discussion about returning of array, in calculation time we will return number base for the sum of that array
numberBase LAMBDA( number, ROUND( number/10^INT( LOG10(number) ), 2) );
Okay, let start with calculation time. Slightly modified function from the sample could be
// Timing a computation wrapped in a thunk // Time is in milliseconds (resolution 10ms on desktop) calculationTimeA = LAMBDA(thunk, LET( start, NOW() , value, thunk() , end, NOW() , days, end - start , ms, days * 24 * 60 * 60 * 1000 , ms_round, round(ms,0) , "Calc time: " & ms_round & " ms " ) );
Calling it we have
Fine, but we see that re-calculation actually takes much more. How much depends on your PC and function parameters, in any case that's significantly more. You may check more exactly opening Timer on your mobile and click simultaneously on it and Enter in Excel. Why so? Excel doesn't evaluate thunk function if it is not required in output. What value in above do it only gets the pointer on the code. It is not evaluated. Let try to add the result
calculationTimeC = LAMBDA(thunk LET( start, NOW() , value, thunk() , end, NOW() , days, end - start , ms, days * 24 * 60 * 60 * 1000 , ms_round, round(ms,0) , "Calc time: " & ms_round & " ms -> " & numberBase(SUM(value)) ) )
Result is
Here value is still the pointer on code, no real value is returned , thus wrong reference.
Let try to avoid thunk and call the function directly
calculationTimeB = LAMBDA LET( start, NOW() , value, sample.test() , end, NOW() , days, end - start , ms, days * 24 * 60 * 60 * 1000 , ms_round, round(ms,0) , "Calc time: " & ms_round & " ms" ) );
Thunk is not the parameter, function is called directly and calculation time is about the same as we have on manual timer:
That's great, but we still want to use thunk not to be dependant on concrete function. Let try to call thunk with LAMBDA within our function
calculationTimeD = LAMBDA(thunk LET( start, NOW() , value, LAMBDA(thunk)() , end, NOW() , days, end - start , ms, days * 24 * 60 * 60 * 1000 , ms_round, round(ms,0) , "Calc time: " & ms_round & " ms -> " & numberBase(SUM(value)) ) )
Bit better
but still not what is expected. value is evaluated not between start and end, but on the final step when we use it. What to do? Use function with just thunk
calculationTime = LAMBDA(thunk LET( start, NOW() , value, thunk() , end, NOW() , days, end - start , ms, days * 24 * 60 * 60 * 1000 , ms_round, round(ms,0) , "Calc time: " & ms_round & " ms -> " & numberBase(SUM(value)) ) )
but call thunk wrapping with lambda
That is exactly what we'd like to have. Another way is to wrap our initial function
callTest=LAMBDA( test )
and call it directly.
Technical note. Don't expect exact calculation time using above. It'll be approximate value which is changed every time you press F9. Press few times and take average to be more exact.
Ex-Big4 Consultant || Valuation, Modeling, Analytics || 60K+ Followers || MS Excel (Spreadsheet) Expert || Project Finance || Trainer & Cool Mentor || Belief - Drafted solutions ☑️ ; Problem Talker ❌ || Citizen Activist
1yI would request you to make a video, with the standard data problems we face at work. Despite, working with Lambdas, I am not able to understand this concept. It's a bouncer for me.
Excel and VBA Specialist @ jkp-ads.com, Microsoft MVP
3yComplicated stuff Sergei Baklan! Still not sure how one would put this to use in the real world
Data-driven by nature
3yI'm going to need to read this one a few times to wrap my head around it! I do know that I wish it wasn't called "thunk". I find it jarring. Oh well, I suppose that's what happens when programmers are locked in a room with no daylight.