Description: The following gives an error: =LET( chars,{"A","B"}, upper,FIND(chars,"ABCDEFGHIJKLMNOPQRSTUVWXYZ"), COLUMNS(upper)) .... error Err:504 But these 2 functions work: =LET( chars,{"A","B"}, upper,FIND({"A","B"},"ABCDEFGHIJKLMNOPQRSTUVWXYZ"), COLUMNS(upper)) .... no error, result 2 =LET( chars,{"A","B"}, upper,FIND(chars,"ABCDEFGHIJKLMNOPQRSTUVWXYZ"), COLUMNS(chars)) .... no error, result 2 Steps to Reproduce: 1. Enter the following function in a cell: =LET( chars,{"A","B"}, upper,FIND(chars,"ABCDEFGHIJKLMNOPQRSTUVWXYZ"), COLUMNS(upper)) 2. Result: Err:504 instead of 2 3. But the following 2 functions give the correct result of 2: 4. =LET( chars,{"A","B"}, upper,FIND({"A","B"},"ABCDEFGHIJKLMNOPQRSTUVWXYZ"), COLUMNS(upper)) 5. =LET( chars,{"A","B"}, upper,FIND(chars,"ABCDEFGHIJKLMNOPQRSTUVWXYZ"), COLUMNS(chars)) 3. Actual Results: see above Expected Results: see above Reproducible: Always User Profile Reset: No Additional Info: no other info
Try Ctrl + Shift + Enter *** This bug has been marked as a duplicate of bug 169718 ***
no change with ctrl+shift+enter
Cannot reproduce the issue ( in case I enter correctly as array-formula ) apart that the Formula doesnt make any sense… use: =COLUMNS(A:B)
This case is only for testing to show the bug in LET / FIND functions. FIND returns no array if the first argument in FIND is a local variable of LET containing an array. But if the first argument of FIND is an array constant, e.g. {"A","B"} everything is fine.
Sorry, I tried once more - now it is working if I press shift+ctrl+enter !!!!
(In reply to nib70776 from comment #5) > Sorry, I tried once more - now it is working if I press shift+ctrl+enter !!!! Closing.
*** This bug has been marked as a duplicate of bug 169718 ***
When the result is a single cell and a numeric value, the SUMPRODUCT function can sometimes handle the array by avoiding array mode. This was a well-known method in Excel too. It might be useful if you need interoperability. SUMPRODUCT https://help.libreoffice.org/latest/en-US/text/scalc/01/04060107.html?DbPAR=CALC#bm_id3163286 SUMPRODUCT returns a single number, it is not necessary to enter the function as an array function. The SUMPRODUCT function won't work perfectly in every case, but the sample formula provided displays the correct value. =SUMPRODUCT(LET(chars,{"A","B"}, upper, FIND(chars, "ABCDEFGHIJKLMNOPQRSTUVWXYZ"), COLUMNS(upper))) --> 2
How should a name be assigned to a range within the Let() function https://ask.libreoffice.org/t/how-should-a-name-be-assigned-to-a-range-within-the-let-function/130855