Feb 27 2024 01:58 PM
Hello everyone,
Could you explain to me the difference in results between these 2 formulas (simplified version).
Formula 1 (works well):
=LAMBDA(nbC,nbR,LET(Tab,MAKEARRAY(nbR,nbC,LAMBDA(r,c,c&"-"&r)),IFERROR(CHOOSECOLS(Tab,2),"KO")))(6,1) Result => 2-1
=LAMBDA(nbC,nbR,LET(Tab,MAKEARRAY(nbR,nbC,LAMBDA(r,c,c&"-"&r)),IFERROR(CHOOSECOLS(Tab,2)/0,"KO")))(6,1) Result => KO
Formula 2 (stutters):
=LAMBDA(nbC,nbR,LET(Tab,MAKEARRAY(nbR,nbC,LAMBDA(r,c,c&"-"&r)),IFERROR(CHOOSECOLS(Tab,2),Tab)))(6,1) Result =>2-1 2-1 2-1...
=LAMBDA(nbC,nbR,LET(Tab,MAKEARRAY(nbR,nbC,LAMBDA(r,c,c&"-"&r)),IFERROR(CHOOSECOLS(Tab,2)/0,Tab)))(6,1) Result => 1-1 2-1 3-1...
In my real formula, in the event of an error I would like to return the entire table as in formula 2. But with this formula Excel starts repeating the result when there is no error!
Another problem
I'm trying to:
I use CHOOSECOLS but I also get a repetition of the results (cross product)
How to prevent this?
With MAP I get nested table!
Regards
Arnaud
Feb 27 2024 03:58 PM
SolutionWhat is happening in 'Formula 2' is that the presence of the array in the IFERROR condition causes the result of CHOOSECOLS(Tab, 2) to broadcast to match the error argument in size. I think the problem may be that CHOOSECOLS(Tab, 2) is still of TYPE 64 (array) despite it returning only one value.
Using '@' to truncate the supposed array may sort this problem
=LAMBDA(nbC, nbR,
LET(
Tab, MAKEARRAY(nbR, nbC, LAMBDA(r, c, c & "-" & r)),
IFERROR(@CHOOSECOLS(Tab, 2), Tab)
)
)(6, 1)
Feb 27 2024 04:58 PM
Thank you for your answer.
I'm going to go to bed less stupid 😁
For my other problem, I solved it by using an INDEX function instead of CHOOSECOLS.
I apply a matrix {1,2} for exemple with SEQUENCE to the row argument.
Feb 27 2024 03:58 PM
SolutionWhat is happening in 'Formula 2' is that the presence of the array in the IFERROR condition causes the result of CHOOSECOLS(Tab, 2) to broadcast to match the error argument in size. I think the problem may be that CHOOSECOLS(Tab, 2) is still of TYPE 64 (array) despite it returning only one value.
Using '@' to truncate the supposed array may sort this problem
=LAMBDA(nbC, nbR,
LET(
Tab, MAKEARRAY(nbR, nbC, LAMBDA(r, c, c & "-" & r)),
IFERROR(@CHOOSECOLS(Tab, 2), Tab)
)
)(6, 1)