[Libreoffice-bugs] [Bug 109096] GEOMEAN gives Err:502 when an argument is zero

[bugzilla-daemon at bugs.documentfoundation.org]

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

Winfried Donkers <winfrieddonkers at libreoffice.org> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
             Status|UNCONFIRMED                 |ASSIGNED
           Assignee|libreoffice-bugs at lists.free |winfrieddonkers at libreoffice
                   |desktop.org                 |.org
     Ever confirmed|0                           |1

--- Comment #2 from Winfried Donkers <winfrieddonkers at libreoffice.org> ---
(In reply to Julien Nabet from comment #1)
> I suppose since Excel doesn't accept <= 0 values (see
> https://support.office.com/en-us/article/GEOMEAN-function-db1ac48d-25a5-40a0-
> ab83-0b38980e40d5), it won't be too on LO for compatibility.
> 
> Winfried: thought you might be interested in this one.

Well, this requires some mathematical explanation.
The geometric mean is defined as 
  ROOTn( a1*a2*...*an )
This definition clearly allows 0 as value for any a.
Unfortunately, on computers the product may well rise above the maximum value
that can be stored, particularly with large values and a large number of
values.

There as also another definition for geometric mean, which does not have this
overflow problem: 
  EXP( ( ln(a1)+ln(a2)+...+ln(an) ) / n )  (with all values > 0)
or 
  -1^(m/n) * EXP( ( ln(|a1|)+ln(|a2|)+...+ln(|an|) ) / n )  (with m values < 0)
This definition is generally used in computer applications, but has the
disadvantage that it cannot accept any value of 0.

However, when looking at the first definition, it is clear that with any value
being 0 the geometric mean will be 0, regardless of n and other values.
This means that when computing the second algorithm, a result of 0 can be
returned at the moment a value of 0 is read.

reference: https://en.wikipedia.org/wiki/Geometric_mean

GEOMEAN is defined in ODFF1.2 without constraints, so changing the behaviour as
described directly above will comply with ODFF. The interoperability with Excel
is reduced in so far that Calc accepts more than Excel, with Calc being
mathematically correct.

It'll be easy to fix.
At the same time I will fix another issue (not yet reported): negative values
are not accepted yet.

-- 
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/20170725/23614631/attachment-0001.html>