Bug 128800 - INDEX function insufficiently documented (see comment 8)
Summary: INDEX function insufficiently documented (see comment 8)
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Documentation (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Calc-Function
  Show dependency treegraph
 
Reported: 2019-11-14 13:54 UTC by knittel
Modified: 2019-11-18 18:49 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:


Attachments
inconsistentcy in function wizard (45.60 KB, image/png)
2019-11-14 13:54 UTC, knittel
Details
index example (8.49 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2019-11-14 19:08 UTC, Oliver Brinzing
Details
INDEX comparison of same formulas with different ranges. (9.62 KB, application/vnd.oasis.opendocument.spreadsheet)
2019-11-15 00:55 UTC, knittel
Details
inconsistency in function wizard marked (56.76 KB, image/png)
2019-11-15 01:08 UTC, knittel
Details
INDEX_test1_with_semicolon.xlsx (5.66 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2019-11-15 18:48 UTC, Oliver Brinzing
Details

Note You need to log in before you can comment on or make changes to this bug.
Description knittel 2019-11-14 13:54:14 UTC
Created attachment 155815 [details]
inconsistentcy in function wizard

Single row/column range with the INDEX function behave inconsistent.

When selecting a single row/column range the documentation states:
"If reference is a one-dimensional column vector, column
is optional or can be omitted. If reference is a one-
dimensional row vector, row is optional, which effectively
makes row act as the column offset into the vector, or can
be omitted."

But the real behaviour is that the optional column/row parameters 'have' to be omitted. When entering the function manually or in the function wizard and specify '1' as the row(/column) in a single row(/column) array the value gets used as the column(/row) index instead. (see screenshot)

try: 
B1:B2 {=INDEX(A1:A2,1)}

real behaviour: the target area gets filled with the content of first cell of the source array. B1=A1 B2=A1
expected behaviour (option1): the target area gets filled with the first row of the source array: B1=A1 B2=A2
expected behaviour (option2): the function wizard and the tooltip detect that the source range/array is a single column/row and reflect that in the interface somehow. (this may be hard if the source range has to be calculated)
Comment 1 knittel 2019-11-14 16:33:07 UTC
an even more severe problem is that if you change the dimensions of the source range to/from a single column/row the INDEX function changes behaviour without notification.
If source is a named ranged you are even less aware of the consequences of your change.

aaand it's very incopatible to excels INDEX function:
https://support.office.com/en-us/article/index-function-a5dcf0dd-996d-40a4-a822-b56b061328bd
Comment 2 Oliver Brinzing 2019-11-14 19:08:38 UTC
Created attachment 155825 [details]
index example
Comment 3 Oliver Brinzing 2019-11-14 19:11:56 UTC
Thank you for reporting the bug. 

I added an excel example spreadsheet, it seems excel calculates same way.

Please attach a sample document, as this makes it easier for us to verify the bug. 
(Please note that the attachment will be public, remove any sensitive information before attaching it. 
See https://wiki.documentfoundation.org/QA/FAQ#How_can_I_eliminate_confidential_data_from_a_sample_document.3F for help on how to do so.)

I have set the bug's status to 'NEEDINFO'. Please change it back to 'UNCONFIRMED' once the requested document is provided.
Comment 4 knittel 2019-11-15 00:55:19 UTC
Created attachment 155828 [details]
INDEX comparison of same formulas with different ranges.

example file for clarification.
Comment 5 knittel 2019-11-15 01:06:08 UTC
i would also like to emphasize, that these are probably two separate problems?
one is the handling of the parameters and the other one is, that the function wizard / tooltip is inconsistent with the actual usage of parameters in the formula.
Comment 6 knittel 2019-11-15 01:08:22 UTC
Created attachment 155829 [details]
inconsistency in function wizard marked

marked important stuff with red circles ;)
Comment 7 Oliver Brinzing 2019-11-15 18:48:51 UTC
Created attachment 155861 [details]
INDEX_test1_with_semicolon.xlsx

(In reply to knittel from comment #6)
> Created attachment 155829 [details]
> inconsistency in function wizard marked
> 
> marked important stuff with red circles ;)

i checked with excel 2016:
F9:H9   =INDEX(range2;1) -> {1,1,1}
B9:D9   =INDEX(range1;1) -> REF!
D17:D19 =INDEX(range1;1) -> REF!

but i think i found the root case:

excel needs a trailing semicolon:
F9:H9   =INDEX(range2;1;) -> {1,2,3}
B9:D9   =INDEX(range1;1;) -> {1,2,3}
D17:D19 =INDEX(range1;1;) -> {1,1,1}

and adding a ";" in LO makes F9:H9 work too:
F9:H9 =INDEX(range2;1;)  -> {1,2,3}

@eike: you might be interested in this
Comment 8 Eike Rathke 2019-11-18 18:49:01 UTC
The INDEX() function is always good for some confusion.. ;-)

What happens here with  {=INDEX(range2;1)}  is that range2 is a one-dimensional row vector F4:H4, in which case if the Column argument is entirely omitted the Row argument acts as a column offset into the vector. The function then returns a reference to a single cell, here F4, which for the array result is repeated as often as necessary.

Specifying the Column argument as empty like in  {=INDEX(range2;1;)}  lets the function return a row vector of the width of the given range, here the first of (the only) F4:H4. Same with Column 0 as in  {=INDEX(range2;1;0)}

That Excel does not handle  {=INDEX(range1;1)}  as the first row vector of B4:D6 to return B4:D4 and gives #REF! in that case seems to be their implementation detail. There is no actual reason not to do so.

I agree that our help is lacking details, compare with
https://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html#INDEX
and also note the more detailed syntax there

  INDEX( ReferenceList|Array DataSource ; [ Integer Row ] [ ; [ Integer Column ] ] [ ; Integer AreaNumber = 1 ] )

regarding optional and omitted parameters.

Forwarding to documentation.