microsoft / advanced-formula-environment

Create, edit, and reuse formulas in Excel
https://aka.ms/get-afe
MIT License
109 stars 11 forks source link

How timer LAMBDA function works from Lib.md? #47

Open dlealv opened 1 year ago

dlealv commented 1 year ago

I was able to run go LAMBDA function which uses timer function. But when I try to call it alone I am getting an #CALC! error:

image

Thanks,

jack-williams commented 1 year ago

Timer take a ‘thunk’ as the first argument; a thunk is a zero argument lambda. It should be myLib.timer(LAMBDA(TEXTSPLIT(…))).

dlealv commented 1 year ago

Thanks, I tested this option too. Now I just copied the following function to my workbook:

pair = LAMBDA(x_1,x_2, LAMBDA(j,switch(j,1,x_1,2,x_2)));
timer = LAMBDA(thunk,
  LET( time_0, NOW()
     , value, thunk()
     , time_1, NOW()
     , days, time_1 - time_0
     , ms, days * 24 * 60 * 60 * 1000 // milliseconds (resolution 10ms on desktop)
     , pair(round(ms,0),value)
     )
);

and then execute the following:

image
dlealv commented 1 year ago

@jack-williams I guess it should not give an error, but at least I found a way to use it, since it uses the pair function output, you can use it as follows: timer(LAMBDA(TEXTSPLIT(…)))(1) which returns the time timer(LAMBDA(TEXTSPLIT(…)))(2) which returns TEXTSPLIT output

I don't think it is intended for the user's final function, you can use it as follows instead: =LET(x,timer(LAMBDA(UPPER("hola"))), HSTACK(x(1), x(2)))

or even shorter: =timer(LAMBDA(UPPER("hola")))({1,2})

but it doesn't work properly when the result is an array:

image

As you can see using HSTACK works in both cases.

jack-williams commented 1 year ago

I forgot that the pair function uses a function encoding. This is the problem. I think it would be better to replace this with HSTACK now, which at the time was not available.

pair = LAMBDA(x_1,x_2, HSTACK(x_1, x_2));
fst = LAMBDA(p, INDEX(p, 1, 1));
snd = LAMBDA(p, INDEX(p, 1, 2));