Bug 137715 - Spreadsheet functions need clarification that they ignore text
Summary: Spreadsheet functions need clarification that they ignore text
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Documentation (show other bugs)
Version:
(earliest affected)
unspecified
Hardware: All All
: medium normal
Assignee: sdc.blanco
URL:
Whiteboard: target:7.2.0
Keywords:
Depends on:
Blocks:
 
Reported: 2020-10-24 06:17 UTC by Mike Kaganski
Modified: 2021-03-29 13:27 UTC (History)
3 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 Mike Kaganski 2020-10-24 06:17:16 UTC
Help for SUM [1] includes this note:

> SUM ignores any text or empty cell within a range or array. If you suspect
> wrong results of the SUM function, look for text in the data ranges. Use the
> value highlighting feature to highlight the text contents in the range that
> may represent a number.

However, this applies not only to SUM, but also to AVERAGE [2], SUMSQ [3], and PRODUCT [4], all of them not having similar note. They should get it.

[1] https://help.libreoffice.org/7.0/en-US/text/scalc/01/04060106.html?&DbPAR=CALC#Section16
[2] https://help.libreoffice.org/7.0/en-US/text/scalc/01/04060184.html?&DbPAR=CALC#average
[3] https://help.libreoffice.org/7.0/en-US/text/scalc/01/04060106.html?DbPAR=CALC#Section25
[4] https://help.libreoffice.org/7.0/en-US/text/scalc/01/04060106.html?DbPAR=CALC#Section26
Comment 1 Mike Kaganski 2020-10-24 07:10:04 UTC
The functions taking NumberSequence, NumberSequenceList, or DateSequence (all of which declare the same conversion of the reference values into numbers), can be checked in [1].

[1] https://docs.oasis-open.org/office/OpenDocument/v1.3/OpenDocument-v1.3-part4-formula.html
Comment 2 sdc.blanco 2020-11-11 12:29:41 UTC
(In reply to Mike Kaganski from comment #1)
> The functions taking NumberSequence, NumberSequenceList, or DateSequence
> (all of which declare the same conversion of the reference values into
> numbers), can be checked in [1].
Was unsure if this has implications for help pages, beyond what is described in comment 0.
Comment 3 Mike Kaganski 2020-11-11 12:37:52 UTC
(In reply to sdc.blanco from comment #2)

I suppose that *all* function taking such arguments are affected, and need that note.

Maybe Eike can correct me if I'm wrong?
Comment 4 sdc.blanco 2020-12-17 09:13:13 UTC
@Eike, can you help us here?

> Help for SUM includes this note:
> 
> > SUM ignores any text or empty cell within a range or array. If you suspect
> > wrong results of the SUM function, look for text in the data ranges. Use the
> > value highlighting feature to highlight the text contents in the range that
> > may represent a number.
https://help.libreoffice.org/7.2/en-US/text/scalc/01/04060106.html?&DbPAR=CALC#Section16

(In reply to Mike Kaganski from comment #0)
> However, this applies not only to SUM, but also to AVERAGE, SUMSQ,
> and PRODUCT, all of them should get a similar note. 
The progress-blocking question: 

Do *all* functions taking NumberSequence, NumberSequenceList, or DateSequence (i.e., declare the same conversion of the reference values into numbers) need that note?
Comment 5 Eike Rathke 2020-12-17 13:06:35 UTC
Yes, by definition all (Date|Number)Sequence* are processed the same ignoring text and empty cells of cell range references.
Comment 6 sdc.blanco 2020-12-17 13:32:28 UTC
(In reply to Eike Rathke from comment #5)
> Yes, by definition all (Date|Number)Sequence* are processed the same
> ignoring text and empty cells of cell range references.

Thanks Eike!  

Over to you Mike.  

Maybe a single note? 

at top of page? 
  ( for Mathematical Functions (scalc/01/04060106), 
  ( for Statistical Functions Part Four (scalc/01/04060184)

listing all applicable functions for that page?

Or....
Comment 7 Mike Kaganski 2020-12-17 13:37:02 UTC
(In reply to sdc.blanco from comment #6)
> Maybe a single note? 
> 
> at top of page? 

How often do you look in other places on a page, when you are directed to some bookmark dedicated to, say, AVERAGE on a ten-kilometer-long page?

Of course, I would love if we drop those long pages, and put each function to its own page. But unless we have it done, the information must be repeated as many times per page as there are affected functions there.
Comment 8 sdc.blanco 2020-12-17 14:43:00 UTC
(In reply to Mike Kaganski from comment #7)
> Of course, I would love if we drop those long pages, and put each function
> to its own page. 
Option 1.  Close this as WF and open new bug with that request, along with requirement for relevant <note> |

Option 2.  Apply the Kaganski principle - make something better until someone else makes it even better.

> But unless we have it done, the information must be
> repeated as many times per page as there are affected functions there.
The four mentioned here are waiting in: https://gerrit.libreoffice.org/c/help/+/105535

Can you identify the others that need this note?
Comment 9 Commit Notification 2021-03-27 08:34:15 UTC
Seth Chaiklin committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/help/commit/11e69d34205bf773f2aa4f4be5c8fa08566ed7d7

tdf#137715 add "note" to AVERAGE, SUMSQ, PRODUCT in Calc function help
Comment 10 sdc.blanco 2021-03-27 08:37:32 UTC
@Mike - your call about whether this bug should be closed, or whether other functions should get the same tip.
Comment 11 Mike Kaganski 2021-03-27 08:54:44 UTC
(In reply to sdc.blanco from comment #10)

See comment 1 and comment 5.
Here's a list of all functions with NumberSequence in arguments:

FVSCHEDULE
IRR
XIRR
MULTINOMIAL
SUBTOTAL
SUMSQ
AVERAGE
DEVSQ
FTEST
MODE
QUARTILE
SKEWP
STDEVP
VAR
VARP

Here's a list of all functions with NumberSequenceList in arguments:

NPV
COUNT
AND
OR
GCD
LCM
PRODUCT
SUM
AVEDEV
FREQUENCY
GEOMEAN
HARMEAN
KURT
LARGE
MAX
MEDIAN
MIN
PERCENTILE
PERCENTRANK
RANK
SKEW
SMALL
STDEV
TRIMMEAN
ZTEST

Here's a list of all functions with DateSequence in arguments:

NETWORKDAYS
WORKDAY
XIRR
Comment 12 sdc.blanco 2021-03-27 11:00:19 UTC
Given the considerable number of functions that need this "tip"....and given that "necessity is the mother of invention"...

...what is your opinion about the following solution, which drops mentioning the function name.  That is, 

Current note mentions function name, e.g.,for SUM:

    SUM ignores any text or empty cell within a range or array. If 
    you suspect wrong results of the SUM function, look for text in the data 
    ranges. Use the <link>value highlighting</link> feature to highlight 
    the text contents in the range that may represent a number.

W/o function name, then:

    This function ignores any text or empty cell within a range or array. If 
    you suspect wrong results of this function, then look for text in the data 
    ranges. Use the <link>value highlighting</link> feature to highlight 
    the text contents in the range that may represent a number.

This "no-function name paragraph" could be translated once, and then embedded in each of the functions that you have listed here. Shall we say, immediately after the "Syntax" statement for the function.

If that is acceptable, then it would avoid the need for retranslation, make it easier to embed the "tip", and avoid the potential problems with the current patch, which uses two <embedvar> to keep the function name in the tip.
Comment 13 sdc.blanco 2021-03-27 11:03:39 UTC
Clarification questions: 
> XIRR
XIRR is listed under both NumberSequence and DateSequence - but (afaict) there is only one function. The classification does not matter for present purposes. Just need to be sure that there is only one XIRR function to get the tip.

And shouldn't NETWORKDAYS.INTL also be on the DateSequence list?
Comment 14 Mike Kaganski 2021-03-27 11:18:30 UTC
(In reply to sdc.blanco from comment #12)
> W/o function name, then:
> 
>     This function ignores any text or empty cell within a range or array... 

LGTM 👍🏼



(In reply to sdc.blanco from comment #13)
> Clarification questions: 
> > XIRR
> XIRR is listed under both NumberSequence and DateSequence

Yes, this is the same function. As said:

> See comment 1

Namely, ODF specifies:

> Syntax: XIRR( NumberSequence Values ; DateSequence Dates [ ; Number Guess = 0.1 ] )

So yes, it uses both NumberSequence and DateSequence in its declaration.

> And shouldn't NETWORKDAYS.INTL also be on the DateSequence list?

Possibly - but that is not a part of ODF, so I can't tell what its formal definition is. Maybe Eike can clarify?
Comment 15 Commit Notification 2021-03-27 14:41:16 UTC
Seth Chaiklin committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/help/commit/4de9606186a78818a49c1f8d687b30d0e09fda9b

tdf#137715 add "note" to DateSequence functions about text in range
Comment 16 Commit Notification 2021-03-27 15:26:46 UTC
Seth Chaiklin committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/help/commit/938d18980a672859c4e9b754df10bf18fa6b1cd4

Revert "tdf#137715 add "note" to AVERAGE, SUMSQ, PRODUCT in Calc function help"
Comment 17 Commit Notification 2021-03-27 21:10:42 UTC
Seth Chaiklin committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/help/commit/9535013762e34c86cc4fa19a1ae94d346899a821

tdf#137715  text in range note for SUM, AVERAGE, SUMSQ, PRODUCT
Comment 18 Commit Notification 2021-03-27 21:11:54 UTC
Seth Chaiklin committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/help/commit/267f7663e5705dcf822f317ad386130b853fd484

tdf#137715 add "text in data range" note to statistical functions
Comment 19 Commit Notification 2021-03-27 21:12:05 UTC
Seth Chaiklin committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/help/commit/155384c51ee22314c18a428ec7c1c773b222e804

tdf#137715 add "text in data range" note to diverse functions
Comment 20 sdc.blanco 2021-03-27 21:18:18 UTC
(In reply to Mike Kaganski from comment #11)
> Here's a list of all functions with NumberSequence in arguments:
Very helpful. Thanks. Now all functions in the list have the "note" added - after syntax and before examples.  

Maybe this ticket can be closed now -- unless Eike identifies additional functions that need the <note> about text in data ranges.
Comment 21 Mike Kaganski 2021-03-27 21:46:16 UTC
Let's close. It can be reopened (or a follow-up filed) if needed, at any time.
Comment 22 Eike Rathke 2021-03-29 12:58:02 UTC
(In reply to Mike Kaganski from comment #14)
> (In reply to sdc.blanco from comment #12)
> > And shouldn't NETWORKDAYS.INTL also be on the DateSequence list?
> 
> Possibly - but that is not a part of ODF, so I can't tell what its formal
> definition is. Maybe Eike can clarify?
Yes, the holiday array is a DateSequence ignoring text cells.
Comment 23 Commit Notification 2021-03-29 13:27:54 UTC
Seth Chaiklin committed a patch related to this issue.
It has been pushed to "master":

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

tdf#137715  NETWORKDAYS.INTL gets "text in data range" note