Bug 136404

Summary: Writer table function COUNT gives different result than in Word
Product: LibreOffice Reporter: NISZ LibreOffice Team <libreoffice>
Component: WriterAssignee: Not Assigned <libreoffice-bugs>
Status: VERIFIED FIXED    
Severity: normal CC: nemeth
Priority: medium    
Version: 7.1.0.0.alpha0+   
Hardware: All   
OS: All   
Whiteboard: target:7.1.0
Crash report or crash signature: Regression By:
Bug Depends on:    
Bug Blocks: 103100    
Attachments: Example file from Word with COUNT table function
Screenshot of the original document side by side in Word and Writer

Description NISZ LibreOffice Team 2020-09-02 14:09:51 UTC 
Created attachment 165029 [details]
Example file from Word with COUNT table function

Recently the COUNT table function was added to Writer.
In Word this counts the number of numeric values in the table.
In Writer however it counts the number of non-empty cells.

Steps to reproduce:
    1. Open attached file in Writer

Actual results:
Cell A1 contains number 3 instead of 2. Cell A2 contains 3 instead of 0.
Also happens if a new COUNT function is inserted from the Formula bar set to count the B1:B3 range. If the cells are empty, COUNT still gives 3 instead of 0.

Expected results:
COUNT counts the numeric values in the range, so empty and text cells are not counted.

LibreOffice details:
Version: 7.1.0.0.alpha0+ (x64)
Build ID: 4c20bb789c7536f63a6844a4cb4489a18cee53c7
CPU threads: 4; OS: Windows 6.3 Build 9600; UI render: Skia/Raster; VCL: win
Locale: en-US (hu_HU); UI: en-US
Calc: CL
Comment 1 NISZ LibreOffice Team 2020-09-02 14:10:10 UTC 
Created attachment 165030 [details]
Screenshot of the original document side by side in Word and Writer
Comment 2 László Németh 2020-09-02 15:18:33 UTC 
Recent COUNT implementation is based on Writer's MEAN(), which counts empty or text fields as zero (so it's different from Calc's AVERAGE).

It seems, for interoperability, it's worth to add real AVERAGE, not only as an alias for MEAN to solve these problems.
Comment 3 Commit Notification 2020-09-04 12:25:55 UTC 
László Németh committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/7dbd1cd44918c50f2540955f908cd0a96fce024c

tdf#136404 DOCX import: ignore NaN cells in table formula

It will be available in 7.1.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 4 László Németh 2020-09-04 12:26:17 UTC 
tdf#136404 DOCX import: ignore NaN cells in table formula

Ignore empty cells or cells with text content in data range
of AVERAGE, COUNT and PRODUCT (the new interoperability
functions in Writer), like MSO does, instead of using
NaN data as zeroes here.

Add AVERAGE, as a new function instead of alias of MEAN
to return error message instead of zero for NaN-only
arguments, like Calc does (Note: also MSO gives empty
result instead of zero).
Comment 5 László Németh 2020-09-04 12:30:28 UTC 
NOTE: MIN() and MAX() are still different, maybe it's worth to fix using a compatibility mode...
Comment 6 NISZ LibreOffice Team 2020-10-08 08:51:08 UTC 
Verified in:

Version: 7.1.0.0.alpha0+ (x64)
Build ID: a3b4831208da615789bd1e2d5660dd130807f504
CPU threads: 4; OS: Windows 10.0 Build 17134; UI render: Skia/Raster; VCL: win
Locale: hu-HU (hu_HU); UI: hu-HU
Calc: threaded

Thanks, László :)