Dear Excel/Calc experts:
I attempted to replace direct reference with parametric reference, using indirect(), in multiplication of multiple logic arrays.
(01.criterion.array = 01.check.cell) * (02.criterion.array <= 02.check.cell) * cell 3
. . . . .
The formula failed upon parametric reference, using indirect(). The failure differs between Excel and Calc, as shown in the summary below.
CalcExceldirect or parametric referenceOKOKOKOKall directfailOKOKindirect(criterion.array)OKOKfailindirect(check.cell)OKOKOKOKindirect(check.cell), stand alone, not in (array = cell)
In addition, in Excel, using F9 manual evaluation on the offending component revealed a strange behavior. Extra enclosing curly braces appeared for parametric references to single cells. F9 would eventually lead to the correct result. The existing failed formula would be wiped out.
I wonder if these failures could be reproduced in other environments. Guidance on debugging my oversight is appreciated. Formulae description is listed below. Could not figure out how to attach the *.xlsx (15KB).
Sincerely,
Ray Jahn
[Hardware + Software]
operating systemspreadsheetMS XP SP 3MS Excel 2007, LO Calc 5.4.7.2MS 10 Home 25H2MS Excel 2010, LO Calc 26.2.6.2MS 11 Pro, 21H2MS Excel 2010, LO Calc 26.2.6.2
*.xlsx format was used on purpose. *.xls format、VBA、Calc Basic、UDF were not used.
[Formulae]
CSE (Control + Shift + Enter) is needed to enter these array formulae, instead of simple Enter.
Given any row in a data sheet (02.data), locate the matching criterion row in a separate parameter sheet (02.prm).
01. date.01 (02.prm) <= date.txn (02.data) <= date.02 (02.prm).02. label (02.data) = label (02.prm).
The semantic formula (02.data, column A) of array comparison becomes:
(range.date.01 <= cell.date.txn) *(cell.date.txn <= range.date.02) *(cell.label = range.label)
or code of direct reference,
header 1= match( 1,('02.prm'!$A$3:$A$6 <= $B3) *($B3 <= '02.prm'!$B$3:$B$6) *($C3 = '02.prm'!$C$3:$C$6), 0 )
[code in each worksheet]
sheet 01.data, OK when all direct references.
'01.data'!A10 → = match( 1, ($A$3:$A$6<=$B10)*($B10<=$B$3:$B$6)*($C10=$C$3:$C$6), 0 )
sheet 02.prm, parameters for parametric references.
'02.prm'!B10 → = "'02.prm'!$A$" & row('02.prm'!A$2)+1 & ":$A$" & row('02.prm'!A$7)-1'02.prm'!B11 → = "'02.prm'!$B$" & row('02.prm'!B$2)+1 & ":$B$" & row('02.prm'!B$7)-1'02.prm'!B12 → = "'02.prm'!$C$" & row('02.prm'!C$2)+1 & ":$C$" & row('02.prm'!C$7)-1
sheet 03.data, part 1.
'03.data'!A03 → = match( 1, ('02.prm'!$A$3:$A$6<=$B3)*($B3<='02.prm'!$B$3:$B$6)*($C3='02.prm'!$C$3:$C$6), 0 )'03.data'!A05 → = indirect( '02.prm'!$B$15&row() )'03.data'!A06 → = match( 1, --('02.prm'!$B$3:$B$6<=$B6), 0 )'03.data'!A07 → = match( 1, --('02.prm'!$B$3:$B$6 <= indirect( "$B"&row() )), 0 )'03.data'!A08 → = match( 1, --('02.prm'!$B$3:$B$6 <= indirect('02.prm'!$B$15&row()), 0 )'03.data'!A10 → = indirect( '02.prm'!$B$15&row() )'03.data'!A11 → = match( 1, --($B11 <= '02.prm'!$B$3:$B$6), 0 )'03.data'!A12 → = match( 1, --(indirect("$B"&&row()) <= '02.prm'!$B$3:$B$6), 0 )'03.data'!A13 → = match( 1, --(indirect('02.prm'!$B$15&row()) <= '02.prm'!$B$3:$B$6), 0 )
sheet 03.data, part 1, Excel.
sheet 03.data, part 1, Calc.
sheet 03.data, part 2.
'03.data'!A15 → = indirect( '02.prm'!$B$16&row() )'03.data'!A16 → = match( 1, --($C16 = '02.prm'!$C$3:$C$6), 0 )'03.data'!A17 → = match( 1, --(indirect("$C"&row()) = '02.prm'!$C$3:$C$6), 0 )'03.data'!A18 → = match( 1, --(indirect('02.prm'!$B$16&row()) = '02.prm'!$C$3:$C$6), 0 )'03.data'!A20 → = match( 1, --($C20 = '02.prm'!$C$3:$C$6), 0 )'03.data'!A21 → = match( 1, --($C21 = indirect('02.prm'!$B$12)), 0 )'03.data'!A22 → = match( 1, --(indirect('02.prm'!$B$16&row()) = '02.prm'!$C$3:$C$6), 0 )'03.data'!A23 → = match( 1, --(indirect('02.prm'!$B$16&row()) = indirect('02.prm'!$B$12)), 0 )'03.data'!A25 → = match( 1, --(indirect('02.prm'!$B$16&row()) = indirect('02.prm'!$B$16)), 0 )
sheet 03.data, part 2, Excel.
sheet 03.data, part 2, Calc.
