•2 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
BYROW/BYCOL/MAP Variants for Nested Arrays + BENCHMARK
Hey everyone!
I made some simple BYROW, BYCOL, and MAP variants that can return nested arrays, and I also made a BENCHMARK function for performance testing.
Here's some code for testing:
BYROW⊟ = LAMBDA(array, function, [orient], LET( me, LAMBDA(me, seg, LET( n, ROWS(seg), IF( n = 1, function(seg), IF( orient, HSTACK( me(me, TAKE(seg, INT(n / 2))), me(me, DROP(seg, INT(n / 2))) ), VSTACK( me(me, TAKE(seg, INT(n / 2))), me(me, DROP(seg, INT(n / 2))) ) ) ) ) ), IFNA(me(me, array), "") ) ); 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" ) ) ); I didn’t put a huge amount of effort into polishing this but In my tests on my device, these performed a lot better than using REDUCE + VSTACK for the same kind of thing, so maybe it’ll be useful to someone.
Really curious to see how people use it, and if something looks like it should be optimized or changed, say so.
I'll update them regularly, fix bugs whenever I can.
You can find the rest of them on my Gist pages: BENCHMARK, SpillNest.
[link] [comments]
Want to read more?
Check out the full article on the original site
Tagged with
#no-code spreadsheet solutions
#Excel alternatives for data analysis
#natural language processing for spreadsheets
#generative AI for data analysis
#rows.com
#Excel compatibility
#big data performance
#Excel alternatives
#BYROW
#BYCOL
#MAP
#nested arrays
#BENCHMARK
#LAMBDA
#performance testing
#ROWS
#HSTACK
#VSTACK
#REDUCE
#NOW