Re: ODF 1.3 ForceArray attribute?
Hi,
On 2024/01/27 20:06:17 Damjan Jovanovic wrote:
> On Sat, Jan 27, 2024 at 5:02 PM Czesław Wolański wrote:
> (...)
> > Please give an example of a non-functioning intersection.
> > AFAICT it works as per specification
> > (in short: return the value from the cell on the same row or column as the
> > formula;
> > otherwise, return an Error).
> >
> If you fill A1-A5 with:
> Letters
> a
> b
> c
> d
>
> then go to C3, and type =VLOOKUP('Letters', 'Letters', 1, 0)
> you'll get an error.
>
> If on another page or another spreadsheet you fill A1-A5 with:
> Numbers
> 15
> 16
> 17
> 18
>
> then go to C3 and type =VLOOKUP('Numbers', 'Numbers', 1, 0)
> you'll get 16.
>
> Why? Apparently because (at least in AOO) intersection only works correctly
> for numerical values, not strings.
> Even in C4 if you just do ='Numbers' you'll get 17, but if you do
> ='Letters' on the other page/spreadsheet's C4 you'll get a 0 instead of "c".
Seeing is believing, thank you!
LibreOffice is affected too.
For strings it seems to work correctly when using
- a named range (Insert ⟶ Names ⟶ Define)
or
- a database range (Data ⟶ Define Range).
>
> Is that a bug?
>
I do think so.
Calc Help topic "Recognizing Names as Addressing" states:
"This function is active by default. To turn this function off,
choose Tools - Options - OpenOffice Calc - Calculate and
clear the Automatically find column and row labels check box."
You will find a similar statement in LibreOffice 7.4 Help [1],
but in LibreOffice 7.5 Help one reads [2]:
"Automatically finding labels is a legacy feature and deactivated by default
as it can produce nondeterministic behavior depending on actual document
content.
To turn this function on, choose Tools - Options - LibreOffice Calc - Calculate
and
mark the Automatically find column and row labels check box.
Using defined labels instead is always possible and behaves similar but in a
defined way."
It looks like the right path for AOO as well.
The user should be warned of the risks involved and
viable alternative(s) should be pointed out.
Regards,
Czesław
---=== Ref. ===---
LibreOffice Help for Calc, topic "Recognizing Names as Addressing"
[1] version 7.4
https://help.libreoffice.org/7.4/en-US/text/scalc/guide/address_auto.html#par_id3155443
[2] version 7.5
https://help.libreoffice.org/7.5/en-US/text/scalc/guide/address_auto.html#par_id3155443
-
To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
For additional commands, e-mail: dev-h...@openoffice.apache.org
Re: ODF 1.3 ForceArray attribute?
On Sat, Jan 27, 2024 at 5:02 PM Czesław Wolański wrote:
> Hi Damjan,
>
> On 2024/01/23 16:42:52 Damjan Jovanovic wrote:
> > Hi
> >
> > Can someone please explain to me what the "ForceArray" attribute is
> > supposed to do?
> >
>
> Just my two cents. I'll be happy to be proven wrong.
>
> >
> > To quote from
> >
> https://docs.oasis-open.org/office/OpenDocument/v1.3/os/part4-formula/OpenDocument-v1.3-os-part4-formula.html#__RefHeading__1017986_715980110
> >
> > ---snip---
> > 6.3.4 Force to array context (ForceArray)
> >
> > A ForceArray attribute forces calculation of the argument's expression
> into
> > non-scalar array mode. This means that no implied intersection is
> > performed, instead where a reference to a single cell is expected and
> > multiple cells are provided, iteration over the multiple cells is
> performed
> > and results are stored in an array that is passed on.
> > ---snip---
> >
> > In ODF 1.3, the HLOOKUP, LOGEST, LINEST, MATCH, and VLOOKUP functions are
> > adding ForceArray to some of their parameters.
> >
> > For example VLOOKUP, is supposed to change from ODF 1.2's:
> >
> > VLOOKUP( Any Lookup ; Reference|Array DataSource ; Integer Column [ ;
> > Logical RangeLookup = TRUE() ] )
> >
> > to ODF 1.3's:
> >
> > VLOOKUP( Any Lookup ; *ForceArray* Reference|Array DataSource ; Integer
> > Column [ ; Logical RangeLookup = TRUE() ] )
> >
> > Now yes, already the "Lookup" parameter can be an array, or a range of
> > cells, and if you enter it as an array formula, it will correctly
> populate
> > each cell with a VLOOKUP of each element.
> >
>
> That's right.
>
> >
> >And if you try to enter a column
> > label as the "Lookup", VLOOKUP will fail because it doesn't do "implied
> > intersection".
> >
>
> Please give an example of a non-functioning intersection.
> AFAICT it works as per specification
> (in short: return the value from the cell on the same row or column as the
> formula;
> otherwise, return an Error).
>
>
If you fill A1-A5 with:
Letters
a
b
c
d
then go to C3, and type =VLOOKUP('Letters', 'Letters', 1, 0)
you'll get an error.
If on another page or another spreadsheet you fill A1-A5 with:
Numbers
15
16
17
18
then go to C3 and type =VLOOKUP('Numbers', 'Numbers', 1, 0)
you'll get 16.
Why? Apparently because (at least in AOO) intersection only works correctly
for numerical values, not strings.
Even in C4 if you just do ='Numbers' you'll get 17, but if you do
='Letters' on the other page/spreadsheet's C4 you'll get a 0 instead of "c".
Is that a bug?
> >
> > So why is the ForceArray attribute on the "DataSource" parameter instead
> of
> > the "Lookup" parameter?
> >
> > Or is ForceArray supposed to do something else?
> >
>
> IMHO the ForceArray works as one could expect based on specification:
>
> "(..) no implied intersection is performed, instead where a reference to a
> single cell is expected and multiple cells are provided, iteration over the
> multiple cells is performed and results are
> stored in an array that is passed on."
>
> For example the formula with MODE:
>
> 6.18.50 MODE
> Syntax: MODE( { ForceArray NumberSequence N }+ )
>
> MODE(ABS(A1:A4)) - in normal mode - forces ABS(A1:A4) to be evaluated as
> an array formula.
> ABS() returns an array of the absolute values of A1:A4 from which MODE()
> selects
> the most common value to be returned.
>
Oh so "ForceArray" just changes how the nested function works, forcing eg.
ABS to work on each cell and return an array, instead of working on the
single cell obtained by automatic intersection and returning a single value.
>
> The .ods file available at the link below
>
> https://drive.google.com/file/d/1r0Q5Y1Di6blgirdbul4v-l3S3KEySh0U/view?usp=sharing
>
> contains a few more examples of "ForceArray" in action,
> including LOOKUP vs HLOOKUP / VLOOKUP.
> (The second parameter of the LOOKUP function, unlike HLOOKUP and VLOOKUP,
> is in ODF 1.2 declared as ForceArray).
> Examples with HLOOKUP and VLOOKUP return the #VALUE! error in AOO,
> but yield a value in LibreOffice releases that support ODF 1.3.
>
> I hope you find this useful.
>
>
Yes it's very helpful, thank you!
>
> Best regards,
> Czesław
>
>
Regards
Damjan
Re: ODF 1.3 ForceArray attribute?
Hi Damjan,
On 2024/01/23 16:42:52 Damjan Jovanovic wrote:
> Hi
>
> Can someone please explain to me what the "ForceArray" attribute is
> supposed to do?
>
Just my two cents. I'll be happy to be proven wrong.
>
> To quote from
> https://docs.oasis-open.org/office/OpenDocument/v1.3/os/part4-formula/OpenDocument-v1.3-os-part4-formula.html#__RefHeading__1017986_715980110
>
> ---snip---
> 6.3.4 Force to array context (ForceArray)
>
> A ForceArray attribute forces calculation of the argument's expression into
> non-scalar array mode. This means that no implied intersection is
> performed, instead where a reference to a single cell is expected and
> multiple cells are provided, iteration over the multiple cells is performed
> and results are stored in an array that is passed on.
> ---snip---
>
> In ODF 1.3, the HLOOKUP, LOGEST, LINEST, MATCH, and VLOOKUP functions are
> adding ForceArray to some of their parameters.
>
> For example VLOOKUP, is supposed to change from ODF 1.2's:
>
> VLOOKUP( Any Lookup ; Reference|Array DataSource ; Integer Column [ ;
> Logical RangeLookup = TRUE() ] )
>
> to ODF 1.3's:
>
> VLOOKUP( Any Lookup ; *ForceArray* Reference|Array DataSource ; Integer
> Column [ ; Logical RangeLookup = TRUE() ] )
>
> Now yes, already the "Lookup" parameter can be an array, or a range of
> cells, and if you enter it as an array formula, it will correctly populate
> each cell with a VLOOKUP of each element.
>
That's right.
>
>And if you try to enter a column
> label as the "Lookup", VLOOKUP will fail because it doesn't do "implied
> intersection".
>
Please give an example of a non-functioning intersection.
AFAICT it works as per specification
(in short: return the value from the cell on the same row or column as the
formula;
otherwise, return an Error).
>
> So why is the ForceArray attribute on the "DataSource" parameter instead of
> the "Lookup" parameter?
>
> Or is ForceArray supposed to do something else?
>
IMHO the ForceArray works as one could expect based on specification:
"(..) no implied intersection is performed, instead where a reference to a
single cell is expected and multiple cells are provided, iteration over the
multiple cells is performed and results are
stored in an array that is passed on."
For example the formula with MODE:
6.18.50 MODE
Syntax: MODE( { ForceArray NumberSequence N }+ )
MODE(ABS(A1:A4)) - in normal mode - forces ABS(A1:A4) to be evaluated as an
array formula.
ABS() returns an array of the absolute values of A1:A4 from which MODE() selects
the most common value to be returned.
The .ods file available at the link below
https://drive.google.com/file/d/1r0Q5Y1Di6blgirdbul4v-l3S3KEySh0U/view?usp=sharing
contains a few more examples of "ForceArray" in action,
including LOOKUP vs HLOOKUP / VLOOKUP.
(The second parameter of the LOOKUP function, unlike HLOOKUP and VLOOKUP,
is in ODF 1.2 declared as ForceArray).
Examples with HLOOKUP and VLOOKUP return the #VALUE! error in AOO,
but yield a value in LibreOffice releases that support ODF 1.3.
I hope you find this useful.
Best regards,
Czesław
-
To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
For additional commands, e-mail: dev-h...@openoffice.apache.org
ODF 1.3 ForceArray attribute?
Hi Can someone please explain to me what the "ForceArray" attribute is supposed to do? To quote from https://docs.oasis-open.org/office/OpenDocument/v1.3/os/part4-formula/OpenDocument-v1.3-os-part4-formula.html#__RefHeading__1017986_715980110 ---snip--- 6.3.4 Force to array context (ForceArray) A ForceArray attribute forces calculation of the argument's expression into non-scalar array mode. This means that no implied intersection is performed, instead where a reference to a single cell is expected and multiple cells are provided, iteration over the multiple cells is performed and results are stored in an array that is passed on. ---snip--- In ODF 1.3, the HLOOKUP, LOGEST, LINEST, MATCH, and VLOOKUP functions are adding ForceArray to some of their parameters. For example VLOOKUP, is supposed to change from ODF 1.2's: VLOOKUP( Any Lookup ; Reference|Array DataSource ; Integer Column [ ; Logical RangeLookup = TRUE() ] ) to ODF 1.3's: VLOOKUP( Any Lookup ; *ForceArray* Reference|Array DataSource ; Integer Column [ ; Logical RangeLookup = TRUE() ] ) Now yes, already the "Lookup" parameter can be an array, or a range of cells, and if you enter it as an array formula, it will correctly populate each cell with a VLOOKUP of each element. And if you try to enter a column label as the "Lookup", VLOOKUP will fail because it doesn't do "implied intersection". So why is the ForceArray attribute on the "DataSource" parameter instead of the "Lookup" parameter? Or is ForceArray supposed to do something else? Thank you Damjan
