Bug 116332 - XLSX FILEOPEN Different handling of number and text cells in a function criteria
Summary: XLSX FILEOPEN Different handling of number and text cells in a function criteria
Status: RESOLVED DUPLICATE of bug 75834
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2018-03-10 10:02 UTC by Gabor Kelemen (allotropia)
Modified: 2018-03-11 01:09 UTC (History)
1 user (show)

See Also:
Crash report or crash signature:


Attachments
Example file with the same function referring to numbers formatted as text (16.02 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2018-03-10 10:02 UTC, Gabor Kelemen (allotropia)
Details
Screenshot of the document in LO 6 and Excel 2010 (230.91 KB, image/png)
2018-03-10 10:03 UTC, Gabor Kelemen (allotropia)
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Gabor Kelemen (allotropia) 2018-03-10 10:02:59 UTC
Created attachment 140539 [details]
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
Comment 1 Gabor Kelemen (allotropia) 2018-03-10 10:03:27 UTC
Created attachment 140540 [details]
Screenshot of the document in LO 6 and Excel 2010
Comment 2 m_a_riosv 2018-03-11 01:09:39 UTC

*** This bug has been marked as a duplicate of bug 75834 ***