2 min readfrom Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community

storing a function in a LET variable outside recursion is ~2x slower than calling it directly. Why?

Two versions, only difference is whether the operator is resolved via a LET binding once before recursion, or called hardcoded directly inside it.

This is a toy example that calculates factorial or sum of a given number, but the same pattern shows up in real recursive LAMBDAs.

Version A : operator stored in LET, outside recursion:

FACT_OR_SUM= LAMBDA(n, [mode], LET( op, IF(mode, SUM, PRODUCT), me, LAMBDA(me, k, IF(k <= 1, 1, op(k, me(me, k - 1))) ), me(me, n) ) ) 

=BENCHMARK(LAMBDA(FACT_OR_SUM(170,0)),5000)

Version B: operator hardcoded directly:

Hrd_coded_product= LAMBDA(n, LET( me, LAMBDA(me, k, IF(k <= 1, 1, PRODUCT(k, me(me, k - 1))) ), me(me, n) ) ) 

=BENCHMARK(LAMBDA(Hrd_coded_product(170,0)),5000)

Testing method:

BENCHMARK = LAMBDA(Func, [iterations], [time_unit], LET( iterations, IF(ISOMITTED(iterations), 1, iterations), start_time, NOW(), loop_result, REDUCE(0, SEQUENCE(iterations), LAMBDA(acc, i,Func())), total_ms, (NOW() - start_time) * 86400000, avg, total_ms / iterations, IF(time_unit, "avg: " & TEXT(avg / 1000, "0.000") & "s | total: " & TEXT(total_ms / 1000, "0.000") & "s", "avg: " & TEXT(avg, "0.00") & "ms | total: " & TEXT(total_ms, "0") & "ms" ) ) ); 

op is bound outside me, so you'd expect it to be resolved once. But benchmarking shows Version A is nearly 2x slower than Version B.

Question: Isn't Excel supposed to calculate op once? Is this a known limitation, or is something else going on? Is there a workaround?

submitted by /u/Medohh2120
[link] [comments]

Want to read more?

Check out the full article on the original site

View original article

Tagged with

#no-code spreadsheet solutions
#Excel compatibility
#Excel alternatives for data analysis
#Excel alternatives
#rows.com
#real-time data collaboration
#real-time collaboration
#LET
#LAMBDA
#BENCHMARK
#FACT_OR_SUM
#Hrd_coded_product
#recursion
#operator
#mode
#SUM
#PRODUCT
#iterations
#time_unit
#ISOMITTED
storing a function in a LET variable outside recursion is ~2x slower than calling it directly. Why?