|
 |
access thread: Geometric Mean: Excel has it, does Access 2000?
Message #1 by "Terrence P. Franklin" <tfrankli@k...> on Thu, 11 Oct 2001 14:42:55
|
|
Excel has a function called GEOMEAN. I can find no analogous expression
in Access 2000. Can anyone elaborate?
Thanks.
Message #2 by Walt Morgan <wmorgan@s...> on Thu, 11 Oct 2001 08:46:55 -0500
|
|
It's still there, at least at SR-1 level.
Walt
Message #3 by "Terrence P. Franklin" <tfrankli@k...> on Thu, 11 Oct 2001 15:26:36
|
|
Does it work differently than AVE, MIN, and MAX? Those work but GEOMEAN
gives me an error.
Terry Franklin
It's still there, at least at SR-1 level.
>
> Walt
>
>
Message #4 by Walt Morgan <wmorgan@s...> on Thu, 11 Oct 2001 09:34:49 -0500
|
|
Following is all I was able to glean from Access 2000 help on this:
GEOMEAN
Returns the geometric mean of a range of positive numeric data.
Syntax
GEOMEAN(number1,number2,...)
Number1 Number1, Number2, ... are 1 to 30 numbers or references that
contain numbers for which you want the mean.
Walt
Message #5 by "Pardee, Roy E" <roy.e.pardee@l...> on Thu, 11 Oct 2001 09:01:02 -0700
|
|
I don't believe Access has it--that's a pretty esoteric function for most
database apps.
I can think of two ways to go with this--reference the Excel library & use
automation to call the function from excel something like this:
Public Function GMean(dbl1 As Double, dbl2 As Double, dbl3 As Double, dbl4
As Double) As Double
Dim appExcel As Excel.Application
Set appExcel = New Excel.Application
GMean = appExcel.WorksheetFunction.GeoMean(dbl1, dbl2, dbl3, dbl4)
Set appExcel = Nothing
End Function
The other option would be to write your own function. IIRC, a geometric
mean is the Nth root of the product of the N numbers. So that would be:
' =====================================================
Public Function GMean3(ParamArray intArgs() As Variant) As Double
Dim dblReturn As Double
Dim intN As Integer
Dim i As Integer
dblReturn = 0
intN = 0
' Multiply the args together
For i = LBound(intArgs) To UBound(intArgs)
If IsNumeric(intArgs(i)) Then
intN = intN + 1
If i = LBound(intArgs) Then
dblReturn = CDbl(intArgs(i))
Else
dblReturn = dblReturn * CDbl(intArgs(i))
End If
Else
' what to do with non-numeric args?
End If
Next i
' Now take the Nth root
If intN > 0 Then
dblReturn = dblReturn ^ (1 / intN)
Else
dblReturn = 0
End If
GMean3 = dblReturn
End Function
' =====================================================
You'll want to test that before taking my word for it...
HTH,
-Roy
Roy Pardee
Programmer/Analyst
SWFPAC Lockheed Martin IT
(xxx) xxx-xxxx
-----Original Message-----
From: Terrence P. Franklin [mailto:tfrankli@k...]
Sent: Thursday, October 11, 2001 7:42 AM
To: Access
Subject: [access] Geometric Mean: Excel has it, does Access 2000?
Excel has a function called GEOMEAN. I can find no analogous expression
in Access 2000. Can anyone elaborate?
Thanks.
Message #6 by "Terrence P. Franklin" <tfrankli@k...> on Thu, 11 Oct 2001 17:58:46
|
|
Roy thanks to you and Tom Hamiliton.
I think I wlll try both methods. I did initially think about writing my
own function. Now that Automation has entered my vocabulary, I will also
check into that. I stopped working in Beginning Access 2000 VBA before
their big chapter on Automation. I am also reading about it in A2K
Developer's Handbook vol I.
Thanks.
Terry Franklin--
I don't believe Access has it--that's a pretty esoteric function for most
> database apps.
>
> I can think of two ways to go with this--reference the Excel library &
use
> automation to call the function from excel something like this:
>
> Public Function GMean(dbl1 As Double, dbl2 As Double, dbl3 As Double,
dbl4
> As Double) As Double
> Dim appExcel As Excel.Application
> Set appExcel = New Excel.Application
> GMean = appExcel.WorksheetFunction.GeoMean(dbl1, dbl2, dbl3, dbl4)
> Set appExcel = Nothing
> End Function
>
> The other option would be to write your own function. IIRC, a geometric
> mean is the Nth root of the product of the N numbers. So that would be:
>
> ' =====================================================
> Public Function GMean3(ParamArray intArgs() As Variant) As Double
> Dim dblReturn As Double
> Dim intN As Integer
> Dim i As Integer
>
> dblReturn = 0
> intN = 0
>
> ' Multiply the args together
> For i = LBound(intArgs) To UBound(intArgs)
> If IsNumeric(intArgs(i)) Then
> intN = intN + 1
> If i = LBound(intArgs) Then
> dblReturn = CDbl(intArgs(i))
> Else
> dblReturn = dblReturn * CDbl(intArgs(i))
> End If
> Else
> ' what to do with non-numeric args?
> End If
> Next i
>
> ' Now take the Nth root
> If intN > 0 Then
> dblReturn = dblReturn ^ (1 / intN)
> Else
> dblReturn = 0
> End If
>
> GMean3 = dblReturn
> End Function
> ' =====================================================
>
> You'll want to test that before taking my word for it...
>
> HTH,
>
> -Roy
>
> Roy Pardee
> Programmer/Analyst
> SWFPAC Lockheed Martin IT
> (xxx) xxx-xxxx
>
> -----Original Message-----
> From: Terrence P. Franklin [mailto:tfrankli@k...]
> Sent: Thursday, October 11, 2001 7:42 AM
> To: Access
> Subject: [access] Geometric Mean: Excel has it, does Access 2000?
>
>
> Excel has a function called GEOMEAN. I can find no analogous expression
> in Access 2000. Can anyone elaborate?
> Thanks.
>
|
|
|
View Cart
