Excel, lambdas and thunks with calculation time

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

No alt text provided for this image

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

No alt text provided for this image

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:

No alt text provided for this image

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

No alt text provided for this image

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

No alt text provided for this image

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.

Chinmaya Amte

Ex-Big4 Consultant || Valuation, Modeling, Analytics || 60K+ Followers || MS Excel (Spreadsheet) Expert || Project Finance || Trainer & Cool Mentor || Belief - Drafted solutions ☑️ ; Problem Talker ❌ || Citizen Activist

1y

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

Like
Reply
Jan Karel Pieterse

Excel and VBA Specialist @ jkp-ads.com, Microsoft MVP

3y

Complicated stuff Sergei Baklan! Still not sure how one would put this to use in the real world

Like
Reply
Owen Price

Data-driven by nature

3y

I'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.

To view or add a comment, sign in

Others also viewed

Explore topics