Re: [Libreoffice-qa] [QA] Need someone having Excel

2016-01-15 Thread Eike Rathke 
Hi,

On Thursday, 2016-01-07 13:20:25 -0700, Gérard Fargeot wrote:

> The reporter first, yhen Joel close yhe bug report.
> I'm not sure this is a good idea. The screenshot provided by Miroslav show
> that Excel handles functions in the same way whether the argument either
> text or number.
> =COUNTIF(range;1)
> =COUNTIF(range;"1")
> =COUNTIF(range;"=1")
> provides the same result in Excel with range having text data while Calc
> count only numerical values for the 1st one.
> 
> This apply to COUNTIF(S) and SUMIF(S).
> 
> What is the opinion of dev's about this (Eike or another)? Should we comply
> to Excel in this case or not ?

My take on that is that COUNTIF(range;1) queries a numeric value.
But if Excel does it different and therefor users expect the same
behavior we probably have to change it for interoperability.

  Eike

-- 
LibreOffice Calc developer. Number formatter stricken i18n transpositionizer.
GPG key "ID" 0x65632D3A - 2265 D7F3 A7B0 95CC 3918  630B 6A6C D5B7 6563 2D3A
Better use 64-bit 0x6A6CD5B765632D3A here is why: https://evil32.com/
Care about Free Software, support the FSFE https://fsfe.org/support/?erack


signature.asc
Description: PGP signature
___
List Name: Libreoffice-qa mailing list
Mail address: Libreoffice-qa@lists.freedesktop.org
Change settings: http://lists.freedesktop.org/mailman/listinfo/libreoffice-qa
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://lists.freedesktop.org/archives/libreoffice-qa/

Re: [Libreoffice-qa] [QA] Need someone having Excel

2016-01-15 Thread m.a.riosv 
Hi, 

El 15/01/16 a las 12:31, Eike Rathke escribió:
> Hi,
>
> On Thursday, 2016-01-07 13:20:25 -0700, Gérard Fargeot wrote:
>
> My take on that is that COUNTIF(range;1) queries a numeric value.
> But if Excel does it different and therefor users expect the same
> behavior we probably have to change it for interoperability.
>
>   Eike

Some thoughts:

1) How to avoid break for people having a number as text in a cell, to have
the value without being taken into account in formulas/functions.

1) It makes more complicated to have a criteria for countif only numbers, if
there are a mix of numbers and numbers as text.

2) To have a consistent interpretation,  I think it implies change the
evaluation of text as numbers, from only direct references to consider every
reference in array ranges as a direct reference.

3) What to do with functions like SUM(), evaluate always, when introduced as
array, never.

I think it is a deep change on the restricted behavior, that merits a deep
and reposed discussion.

In ODF 1.2,
http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html#__RefHeading__1017988_715980110,
"
6.3.5Conversion to Number

If the expected type is Number, then if value is of type:

●Number, return it. 

●Logical, return 0 if FALSE, 1 if TRUE. 

●Text: The specific conversion is implementation-defined; an evaluator
may return 0, an Error value, or the results of its attempt to convert the
Text value to a Number (and fall back to 0 or Error if it fails to do so).
Evaluators may apply VALUE() or some other function to do this conversion,
should they choose to do so. Conversion depends on the actual locale the
application runs in, especially if group or decimal separators are involved.
"

Should be numbers what are numbers for VALUE()?, so we can test/know when
the conversion will be done. Should it be in any kind of reference or range
reference?

NumberAsTextInFormulas.ods
 
 



--
View this message in context: 
http://nabble.documentfoundation.org/QA-Need-someone-having-Excel-tp4171047p4171893.html
Sent from the QA mailing list archive at Nabble.com.
___
List Name: Libreoffice-qa mailing list
Mail address: Libreoffice-qa@lists.freedesktop.org
Change settings: http://lists.freedesktop.org/mailman/listinfo/libreoffice-qa
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://lists.freedesktop.org/archives/libreoffice-qa/

[Libreoffice-qa] [QA] Need someone having Excel

2016-01-07 Thread Gérard Fargeot 
Hi all,

For Bug 96923 (https://bugs.documentfoundation.org/show_bug.cgi?id=96923),
we need someone with Excel.
The purpose is to check if Excel treat numbers written as text (with '
before) like numeric 1 in SUMIFS.

If it is the case, can somenone provide a short file with mixed value:
1 written as numeric value,
1 with a ' befrore in cell with standard formatting,
1 in a cell formatted as text.

And various calculation within this data:
Simple SUM, COUNTIF, SUMIF, COUNTIFS, SUMIFS.
and (not sure if necessary) some search functions like (V/H)LOOKUP, MATCH)

I don't have any version of Excel myself.

Gérard



--
View this message in context: 
http://nabble.documentfoundation.org/QA-Need-someone-having-Excel-tp4171047.html
Sent from the QA mailing list archive at Nabble.com.
___
List Name: Libreoffice-qa mailing list
Mail address: Libreoffice-qa@lists.freedesktop.org
Change settings: http://lists.freedesktop.org/mailman/listinfo/libreoffice-qa
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://lists.freedesktop.org/archives/libreoffice-qa/

Re: [Libreoffice-qa] [QA] Need someone having Excel

2016-01-07 Thread Joel Madero 


On 01/07/2016 12:20 PM, Gérard Fargeot wrote:
> The reporter first, yhen Joel close yhe bug report.
> I'm not sure this is a good idea. The screenshot provided by Miroslav show
> that Excel handles functions in the same way whether the argument either
Just to clarify, I closed it after someone else already closed it as
FIXED ;)
___
List Name: Libreoffice-qa mailing list
Mail address: Libreoffice-qa@lists.freedesktop.org
Change settings: http://lists.freedesktop.org/mailman/listinfo/libreoffice-qa
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://lists.freedesktop.org/archives/libreoffice-qa/

Re: [Libreoffice-qa] [QA] Need someone having Excel

2016-01-07 Thread Gérard Fargeot 
The reporter first, yhen Joel close yhe bug report.
I'm not sure this is a good idea. The screenshot provided by Miroslav show
that Excel handles functions in the same way whether the argument either
text or number.
=COUNTIF(range;1)
=COUNTIF(range;"1")
=COUNTIF(range;"=1")
provides the same result in Excel with range having text data while Calc
count only numerical values for the 1st one.

This apply to COUNTIF(S) and SUMIF(S).

What is the opinion of dev's about this (Eike or another)? Should we comply
to Excel in this case or not ?




--
View this message in context: 
http://nabble.documentfoundation.org/QA-Need-someone-having-Excel-tp4171047p4171093.html
Sent from the QA mailing list archive at Nabble.com.
___
List Name: Libreoffice-qa mailing list
Mail address: Libreoffice-qa@lists.freedesktop.org
Change settings: http://lists.freedesktop.org/mailman/listinfo/libreoffice-qa
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://lists.freedesktop.org/archives/libreoffice-qa/

Re: [Libreoffice-qa] [QA] Need someone having Excel

2016-01-07 Thread m.a.riosv 
Hi Gerard,

about comment in A4, If I'm not wrong, modify cell format never changes the
cell content. Formatting the cell as text after enter a number or a formula,
it doesn't convert them to text.

about the criteria as number only summing for numbers in the criteria range,
I think in calc interpreting text as number is only done with direct
references, not with ranges, putting in B6: =SUM(B3:B6) results is 2 not 4
how results with =B3+B4+B5+B6. There are some options in
Menu/Tools/Options/LibreOffice calc/Formula - Detailed Calculation Settings
- Details, but I think don't help in this case.

Regards.
Miguel Ángel.




--
View this message in context: 
http://nabble.documentfoundation.org/QA-Need-someone-having-Excel-tp4171047p4171092.html
Sent from the QA mailing list archive at Nabble.com.
___
List Name: Libreoffice-qa mailing list
Mail address: Libreoffice-qa@lists.freedesktop.org
Change settings: http://lists.freedesktop.org/mailman/listinfo/libreoffice-qa
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://lists.freedesktop.org/archives/libreoffice-qa/

Re: [Libreoffice-qa] [QA] Need someone having Excel

2016-01-07 Thread m.a.riosv 
Related links.

http://erack.org/blog/archives/40-LibreOffice-4.3-new-Calc-feature-user-selectable-text-conversion-models.html

https://wiki.documentfoundation.org/ReleaseNotes/4.3#User_selectable_text_conversion_models

https://bugs.documentfoundation.org/show_bug.cgi?id=37132

https://bugs.documentfoundation.org/show_bug.cgi?id=75834#c1

Regards.
Miguel Ángel.



--
View this message in context: 
http://nabble.documentfoundation.org/QA-Need-someone-having-Excel-tp4171047p4171109.html
Sent from the QA mailing list archive at Nabble.com.
___
List Name: Libreoffice-qa mailing list
Mail address: Libreoffice-qa@lists.freedesktop.org
Change settings: http://lists.freedesktop.org/mailman/listinfo/libreoffice-qa
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://lists.freedesktop.org/archives/libreoffice-qa/