Issue 119942 - [From Symphony]Last agrument of formula should not be removed
Summary: [From Symphony]Last agrument of formula should not be removed
Alias: None
Product: Calc
Classification: Application
Component: open-import (show other issues)
Version: 3.4.0
Hardware: PC All
: P3 Normal (vote)
Target Milestone: 4.0.0
Assignee: AOO issues mailing list
QA Contact:
Depends on:
Reported: 2012-06-11 08:36 UTC by Yan Ji
Modified: 2012-11-07 08:49 UTC (History)
5 users (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: ---
Developer Difficulty: ---

Add patch (1.18 KB, patch)
2012-06-14 03:33 UTC, Zhang Lu
zhanglu.aoo: review?
Details | Diff

Note You need to log in before you can comment on or make changes to this issue.
Description Yan Ji 2012-06-11 08:36:15 UTC
Build: AOO 3.4
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.

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.
Comment 9 Shenfeng Liu 2012-11-07 08:49:51 UTC
set Target Milestone to AOO 3.5.0 for PM purpose.