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
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
(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.
(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?
@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?
Yes, by definition all (Date|Number)Sequence* are processed the same ignoring text and empty cells of cell range references.
(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....
(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.
(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?
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
@Mike - your call about whether this bug should be closed, or whether other functions should get the same tip.
(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
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.
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?
(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?
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
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"
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
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
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
(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.
Let's close. It can be reopened (or a follow-up filed) if needed, at any time.
(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.
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