On Sat, Jan 27, 2024 at 5:02 PM Czesław Wolański <cwo...@apache.org> 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
