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
