Issue 119942

Summary: [From Symphony]Last agrument of formula should not be removed
Product: Calc Reporter: Yan Ji <yanji.yj>
Component: open-importAssignee: AOO issues mailing list <issues>
Status: CLOSED FIXED QA Contact:
Severity: Normal    
Priority: P3 CC: binbjguo, leiw, liushenf, rb.henschel, zhanglu.aoo
Version: 3.4.0   
Target Milestone: 4.0.0   
Hardware: PC   
OS: All   
Issue Type: DEFECT Latest Confirmation in: ---
Developer Difficulty: ---
Attachments:
Description Flags
Add patch zhanglu.aoo: review?

Description Yan Ji 2012-06-11 08:36:15 UTC 
Build: AOO 3.4
Steps:
1. New a excel document, in a cell input formula =AVERAGEA(1,2,3,) 
2. Save it and load in Spreadsheet

Defect: The last comma is removed, and result is different with MS Excel
Comment 1 Regina Henschel 2012-06-11 14:38:33 UTC 
That topic is a very old one. Please have a look at issue 3879. AVERAGEA is not the only problem of this kind. Daniel Rentz has written a list and attached to that issue, which might interest you.
Comment 2 Zhang Lu 2012-06-14 03:33:14 UTC 
Created attachment 78311 [details]
Add patch
Comment 3 Zhang Lu 2012-06-14 03:38:55 UTC 
Root Cause:
When reading Excel 2k3/2k7 doc, comma will be ignored if it is in the end of a formula.

Resolution:
When reading Excel 2k3/2k7 doc, if meet comma in the end of  a formula, add a number '0' (this solution only apply on those function that default value is 0 when meet miss argument, and which can't be skipped. e.g.COUNT, SUM, AVERAGE, MIN, MAX, NPV..., refer to the attachment of issue 3879).
Comment 4 Zhang Lu 2012-06-14 07:18:50 UTC 
In this solution, no matter where empty parameter appeared(beginning, middle or end of the parameter list), value 0 always will be added.
Function list that support this solution as following:
{Count, CountA, Average, Min, MinA, Max, MaxA, StDev, StDevA, Var, VarP, AveDev, Kurt, Schiefe, VarPA, VarA, DevSq}. The set is refer to issue 3879's conclusion.
And get same result with MS Excel.
Comment 5 Wang Lei 2012-06-21 02:06:32 UTC 
I will take care of this
Comment 6 Wang Lei 2012-06-21 05:27:35 UTC 
Reviewed.  Patch looks good.
Thanks for the good work.
Applied on SVN revision 1352412
Comment 7 binguo 2012-08-20 09:48:43 UTC 
Verified on latest AOO Trunk 0817 build on Window OS/Linux OS/Linux 64bit OS, this bug is fixed. The last comma is removed, but currently the result is the same as that in MS Excel.
Comment 8 binguo 2012-10-18 07:34:42 UTC 
Empty argument appears in the end of formula AVERAGEA, like =AVERAGEA(1,2,3,) 
, the argument does not use a number '0' to instead, but remove the 4th argument results in the result is 2, it is different with that in Excel(the result is 1.5). but it works fine with formula AVERAGE. So I close this bug and logged a new one 121230 to check the rest work.

https://issues.apache.org/ooo/show_bug.cgi?id=121230
Comment 9 Shenfeng Liu 2012-11-07 08:49:51 UTC 
set Target Milestone to AOO 3.5.0 for PM purpose.