Bug 139027 - Problems with Help information for Calc's INDEX() function
Summary: Problems with Help information for Calc's INDEX() function
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Documentation (show other bugs)
Version:
(earliest affected)
7.0.3.1 release
Hardware: All All
: medium normal
Assignee: Olivier Hallot
URL:
Whiteboard: target:7.6.0
Keywords:
: 128800 (view as bug list)
Depends on:
Blocks: HelpGaps-NewFeatures
  Show dependency treegraph
 
Reported: 2020-12-18 10:41 UTC by Steve Fanning (Retired)
Modified: 2023-05-07 12:13 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Steve Fanning (Retired) 2020-12-18 10:41:53 UTC
Description:
As part of the GSOD20 work to update the Calc Functions wiki pages, I have been reading the help page for the INDEX() function. Unfortunately, it appears to contain several errors and there are several glaring omissions.

1) There is no mention of using the INDEX function as an array function (either by ticking the Array button in the Function Wizard or by pressing Ctrl+Shift+Enter after typing the formula in the Input line). This is crucial to harness the full power of this function.
2) There is no mention of passing (multi-dimensional) inline array constants to the function, e.g., {=INDEX({1,3,5;7,9,10},{2;1},1)} is a valid array function call.
3) There is no mention of passing a multi-range area using the tilde (~) reference concatenation operator, e.g., =INDEX($B$1:$B$3~$F$1:$F$3,1,1). This could equally be written as =INDEX(($B$1:$B$3,$F$1:$F$3),1,1).
4) The introductory paragraph states that “Depending on context, INDEX returns a reference or content” is probably misleading – I have never seen the function return a “reference”. It can return the content of a single cell or, when entered as an array formula, can return the content of multiple cells.
5) The description of the Reference argument states that “If the reference consists of multiple ranges, you must enclose the reference or range name in parentheses”. This is not true, e.g., =INDEX(TwoAreas,1,1) is okay.
6) The description of the Row argument states that “In case of zero (no specific row) all referenced rows are returned”. This is only true if entered as an array function. Ditto for the corresponding statement for the Column argument.
7) The examples need to be reviewed and updated in the light of the above comments. This function is sufficiently complex and powerful that maybe linking to an ODT file containing a wide range of examples might be worth considering.


Steps to Reproduce:
Search for INDEX in the list of help topics.

Actual Results:
N/A

Expected Results:
N/A


Reproducible: Always


User Profile Reset: No



Additional Info:
Version: 7.0.3.1 (x64)
Build ID: d7547858d014d4cf69878db179d326fc3483e082
CPU threads: 6; OS: Windows 10.0 Build 19041; UI render: Skia/Vulkan; VCL: win
Locale: en-GB (en_GB); UI: en-GB
Calc: threaded
Comment 1 Roman Kuznetsov 2020-12-18 20:03:28 UTC
Eike, could you comment this problem?
Comment 2 Commit Notification 2023-03-22 18:17:56 UTC
Olivier Hallot committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/help/commit/35a7913f2075a9b73fd2c8d41473adface5711bb

tdf#139027 Help information for Calc's INDEX() function
Comment 3 Eike Rathke 2023-04-24 16:50:32 UTC
Just got pinged by this bug when modified (set to resolved) that apparently I missed earlier. For completeness of technical details:

4) INDEX() _does_ return a reference if the argument was one or more references, just the user doesn't see the reference when entering something like =INDEX(...) because of course the reference is resolved and the values displayed, like with any other reference (=A1). In =SUM(INDEX(...)) the SUM() function gets the reference passed that was returned by INDEX(). This may make a difference contrary to resolved values if the function handles a reference different from an array of values.

5) In =INDEX(TwoAreas,1,1) if TwoAreas is the name of a multiple range that of course is one parameter's argument, as is $B$1:$B$3~$F$1:$F$3 or ($B$1:$B$3,$F$1:$F$3)

6) Statement is wrong. "All rows" or "all columns" are also in non-array mode returned. =INDEX($A$1:$A$2;0;1) displays the value of A1 when entered in row 1, of A2 when entered in row 2, and #VALUE! when entered on any other row.
Comment 4 Commit Notification 2023-04-28 21:20:27 UTC
Olivier Hallot committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/help/commit/cac49fecf0b35af451fd0e043b21f86a8a46b38d

tdf#139027 More on INDEX() function
Comment 5 Olivier Hallot 2023-05-07 12:13:17 UTC
*** Bug 128800 has been marked as a duplicate of this bug. ***