[Libreoffice-bugs] [Bug 116332] New: XLSX FILEOPEN Different handling of number and text cells in a function criteria

[bugzilla-daemon at bugs.documentfoundation.org]

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

            Bug ID: 116332
           Summary: XLSX FILEOPEN Different handling of number and text
                    cells in a function criteria
           Product: LibreOffice
           Version: Inherited From OOo
          Hardware: All
                OS: All
            Status: UNCONFIRMED
          Severity: normal
          Priority: medium
         Component: Calc
          Assignee: libreoffice-bugs at lists.freedesktop.org
          Reporter: kelemeng at ubuntu.com

Created attachment 140539
  --> https://bugs.documentfoundation.org/attachment.cgi?id=140539&action=edit
Example file with the same function referring to numbers formatted as text

The main problem here is the cell format which contains a number. If we use a
number as a function criteria and the criteria range contains numbers but the
cell format is TEXT we get a 0 result, unlike the Microsoft Office, which will
return the correct result even if the cell format is text.

Steps to reproduce:
1. Create a new spreadsheet with Microsoft Excel 2010/2013/2016
2. Fill the A1:A3 cells with some number. Fill the B1:B3 cells with some number
but modify the cell format from NUMBER to TEXT (that case the numbers will be
on the left side of the cell).
3. Create two function, which contains a criteria field (for example: Countif)
in the “A4” cell and one in the “B4” cell.
4. The range should be A1:A3 at the first case, and B1:B3 at the second case.
5. The criteria should be just a number in both case. (the number you set as
criteria should be in the range to give back a result if you use Countif)
6. Save the file as *.xslx
7. Open the file with LibreOffice Calc.

Actual results:
Microsoft Excel gives back good result in both case. LibreOffice gives back
good result if the cell format was number, but if the cell format was text, the
result will be 0 in any case. If we use quotation marks at the criteria the
function result will be good.

Expected results:
LibreOffice should handle the number criteria without quotation marks, if the
cell format is text and the cells contains numbers, just like the Microsoft
Excel.
The current behavior "breaks" results in documents that are "broken" because of
numbers formatted as text - but users consider this change as "It worked before
- it does not work now".

Version: 6.0.0.3
Build ID: 64a0f66915f38c6217de274f0aa8e15618924765
CPU threads: 4; OS: Windows 6.1; UI render: default; 
Locale: hu-HU (hu_HU); Calc: group

-- 
You are receiving this mail because:
You are the assignee for the bug.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.freedesktop.org/archives/libreoffice-bugs/attachments/20180310/aabffd85/attachment.html>