Start a new Calc document. 1. Enter 3 into cell A1. 2. Enter 4 into cell A2. 3. Enter =INDIRECT({"A1";"A2"}) into cell C1. Use here and in the following steps instead of ";" the separator which is set in your LibreOffice as Array column separator in tools > Options > Calc > Formula > section 'Separators'. Finish entering with the 'Accept' icon left of the input line. You should get value 3 in cell C1 and value 4 in cell D1. 4. Enter =SUM(INDIRECT({"A1";"A2"})) into cell C2. Finish entering with the 'Accept' icon left of the input line. You should get value 7 in cell C2. 5. Enter =SUM(INDIRECT({"A1";"A2"})) into cell C3. Finish entering with Ctrl+Shift+Enter to force array context. You should get value 7 in cell C3, because SUM function always adds all values even in array context and returns one value. Error: I get a 3 in cell C3 and a 4 in cell C4. 6. Do a hard recalculate, i.e. Ctrl+Shift+F9. It does not correct the error. Save the file and reopen it. Now the value in C3 is 7 as expected. Having a 7 in C4 is an aftereffect of the wrong using of cell C4 in step 5.
Reproducible Version: 25.2.0.0.alpha0+ (X86_64) / LibreOffice Community Build ID: cd4498d32867af26e95de84836b724b4f85ba1b0 CPU threads: 16; OS: Windows 11 X86_64 (10.0 build 22631); UI render: Skia/Raster; VCL: win Locale: es-ES (es_ES); UI: en-US Calc: CL threaded Seems this is a good trick to have cell references in an inner array In step 5 using SUMPRODUCT instead SUM works fine.
Happens the same with a function like AVERAGE.
The problem seems to be the combination aggregate_function(INDIRECT(inline array)) with CSE. Combinations like POWER(INDIRECT(inline array)) or TRANSPOSE(INDIRECT(inline array)) work as expected. @Eike: Simple a bug in LO or has it a special reason?
Created attachment 196678 [details] Some tests with function(INDIRECT(inline array)) The examples show the used formula in the row above the result.