Geometric mean

In one of my recent projects I needed to calculate a geometric mean for a group of numbers.  The Geometric mean formula is generally, if the numbers are , the geometric mean satisfies

The problem appeared when I needed to multiply numbers and got a overflow on the decimal data type in Dynamics NAV.

I produced two solutions to this problem; first for Classic Client using Excel and secondly for Role Tailored Client using Add-in.  Using Excel for this job is slow but the Add-in works great.

In Excel I add values to a column and then use the built in function GEOMEAN(‘<Range>’) to calculate the value.  This works for large values.

The GeoMean Class Add-in I created in Visual Studio in VB.NET
[code htmlscript=”false” lang=”vb”]Public Class GeoMeanClass
Dim TotalValue As Double
Dim NoOfValues As Integer
Public Sub ResetValue()
TotalValue = 1
NoOfValues = 0
End Sub
Public Sub AddValue(ByVal Value As Decimal)
If Value = 0 Then Exit Sub
TotalValue = TotalValue * Value
NoOfValues = NoOfValues + 1
End Sub
Public ReadOnly Property NoOfStoredValues() As Integer
Get
Return NoOfValues
End Get
End Property
Public ReadOnly Property GetGeoMean() As Decimal
Get
If NoOfValues = 0 Then
Return 0
Else
Return CDec(TotalValue ^ (1 / NoOfValues))
End If
End Get
End Property
End Class[/code]
Attached are the Add-in and the codeunits needed to calculate Geometric mean.

GeoMean