Geometric Mean? 
Author Message
 Geometric Mean?

I'm trying to calculate the geometric mean for a
particular field in my database and can't find the
function anywhere.  Does anyone have any
modules/code/suggestions?


Tue, 13 Dec 2005 01:53:02 GMT  
 Geometric Mean?
I've written a function to do this:

Public Function GeometricMean(strTable As String, strField As String) As
Double

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim lngCount As Long
Dim dblProduct As Double

On Error GoTo HandleErrors

strSQL = "SELECT " & strField & " FROM " & strTable
strSQL = strSQL & " WHERE Not IsNull([" & strField & "]);"

Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL, dbOpenForwardOnly)

With rst
    dblProduct = 1
    Do Until .EOF
        dblProduct = dblProduct * rst(0)
        lngCount = lngCount + 1
        .MoveNext
    Loop
    .Close
End With

GeometricMean = dblProduct ^ (1 / lngCount)

ExitHere:
    On Error Resume Next
    Set rst = Nothing
    db.Close
    Set db = Nothing
    Exit Function

HandleErrors:
    Select Case Err.Number
        Case Else
            MsgBox "Unexpected error calculating geometric mean" & vbCr &
Err.Description & " (" & Err.Number & ")"
    End Select
    Resume ExitHere

End Function

I fully expect someone to now post something that works ten times faster
using two lines of code, but this does work


Quote:
> I'm trying to calculate the geometric mean for a
> particular field in my database and can't find the
> function anywhere.  Does anyone have any
> modules/code/suggestions?



Tue, 13 Dec 2005 05:26:00 GMT  
 Geometric Mean?
On Thu, 26 Jun 2003 22:26:00 +0100, "Andrew Smith"

Quote:



>> I'm trying to calculate the geometric mean for a
>> particular field in my database and can't find the
>> function anywhere.  Does anyone have any
>> modules/code/suggestions?

>I've written a function to do this:

>Public Function GeometricMean(strTable As String, strField As String) As
>Double

>Dim db As DAO.Database
>Dim rst As DAO.Recordset
>Dim strSQL As String
>Dim lngCount As Long
>Dim dblProduct As Double

>On Error GoTo HandleErrors

>strSQL = "SELECT " & strField & " FROM " & strTable
>strSQL = strSQL & " WHERE Not IsNull([" & strField & "]);"

>Set db = CurrentDb
>Set rst = db.OpenRecordset(strSQL, dbOpenForwardOnly)

>With rst
>    dblProduct = 1
>    Do Until .EOF
>        dblProduct = dblProduct * rst(0)
>        lngCount = lngCount + 1
>        .MoveNext
>    Loop
>    .Close
>End With

>GeometricMean = dblProduct ^ (1 / lngCount)

>ExitHere:
>    On Error Resume Next
>    Set rst = Nothing
>    db.Close
>    Set db = Nothing
>    Exit Function

>HandleErrors:
>    Select Case Err.Number
>        Case Else
>            MsgBox "Unexpected error calculating geometric mean" & vbCr &
>Err.Description & " (" & Err.Number & ")"
>    End Select
>    Resume ExitHere

>End Function

>I fully expect someone to now post something that works ten times faster
>using two lines of code, but this does work

It can be done in one line :-)

Public Function GeometricMean(strTable As String, strField As String)
As Double

GeometricMean = Exp(DAvg("Log(" & strField & ")", strTable))

End Function

Note that the numbers must be strictly positive. Also, DAvg
automatically disregards Null values, so there is no need for a
condition that excludes Null values.

Greetings
Matthias Kl?y
--
www.kcc.ch



Tue, 13 Dec 2005 16:20:20 GMT  
 Geometric Mean?


Quote:
> On Thu, 26 Jun 2003 22:26:00 +0100, "Andrew Smith"



> >> I'm trying to calculate the geometric mean for a
> >> particular field in my database and can't find the
> >> function anywhere.  Does anyone have any
> >> modules/code/suggestions?

> >I've written a function to do this:

> >Public Function GeometricMean(strTable As String, strField As String) As
> >Double

> >Dim db As DAO.Database
> >Dim rst As DAO.Recordset
> >Dim strSQL As String
> >Dim lngCount As Long
> >Dim dblProduct As Double

> >On Error GoTo HandleErrors

> >strSQL = "SELECT " & strField & " FROM " & strTable
> >strSQL = strSQL & " WHERE Not IsNull([" & strField & "]);"

> >Set db = CurrentDb
> >Set rst = db.OpenRecordset(strSQL, dbOpenForwardOnly)

> >With rst
> >    dblProduct = 1
> >    Do Until .EOF
> >        dblProduct = dblProduct * rst(0)
> >        lngCount = lngCount + 1
> >        .MoveNext
> >    Loop
> >    .Close
> >End With

> >GeometricMean = dblProduct ^ (1 / lngCount)

> >ExitHere:
> >    On Error Resume Next
> >    Set rst = Nothing
> >    db.Close
> >    Set db = Nothing
> >    Exit Function

> >HandleErrors:
> >    Select Case Err.Number
> >        Case Else
> >            MsgBox "Unexpected error calculating geometric mean" & vbCr &
> >Err.Description & " (" & Err.Number & ")"
> >    End Select
> >    Resume ExitHere

> >End Function

> >I fully expect someone to now post something that works ten times faster
> >using two lines of code, but this does work

> It can be done in one line :-)

> Public Function GeometricMean(strTable As String, strField As String)
> As Double

> GeometricMean = Exp(DAvg("Log(" & strField & ")", strTable))

> End Function

> Note that the numbers must be strictly positive. Also, DAvg
> automatically disregards Null values, so there is no need for a
> condition that excludes Null values.

> Greetings
> Matthias Kl?y
> --
> www.kcc.ch

OK, so it can be done in one line, but mine's faster! (Just don't use it
with lot's of big numbers or you'll get an overflow error - probably best to
average the log whichever method is used).


Wed, 14 Dec 2005 02:24:16 GMT  
 Geometric Mean?
On Fri, 27 Jun 2003 19:24:16 +0100, "Andrew Smith"

Quote:



>> On Thu, 26 Jun 2003 22:26:00 +0100, "Andrew Smith"



>> >> I'm trying to calculate the geometric mean for a
>> >> particular field in my database and can't find the
>> >> function anywhere.  Does anyone have any
>> >> modules/code/suggestions?

>> >I've written a function to do this:

>> >Public Function GeometricMean(strTable As String, strField As String) As
>> >Double

>> >Dim db As DAO.Database
>> >Dim rst As DAO.Recordset
>> >Dim strSQL As String
>> >Dim lngCount As Long
>> >Dim dblProduct As Double

>> >On Error GoTo HandleErrors

>> >strSQL = "SELECT " & strField & " FROM " & strTable
>> >strSQL = strSQL & " WHERE Not IsNull([" & strField & "]);"

>> >Set db = CurrentDb
>> >Set rst = db.OpenRecordset(strSQL, dbOpenForwardOnly)

>> >With rst
>> >    dblProduct = 1
>> >    Do Until .EOF
>> >        dblProduct = dblProduct * rst(0)
>> >        lngCount = lngCount + 1
>> >        .MoveNext
>> >    Loop
>> >    .Close
>> >End With

>> >GeometricMean = dblProduct ^ (1 / lngCount)

>> >ExitHere:
>> >    On Error Resume Next
>> >    Set rst = Nothing
>> >    db.Close
>> >    Set db = Nothing
>> >    Exit Function

>> >HandleErrors:
>> >    Select Case Err.Number
>> >        Case Else
>> >            MsgBox "Unexpected error calculating geometric mean" & vbCr &
>> >Err.Description & " (" & Err.Number & ")"
>> >    End Select
>> >    Resume ExitHere

>> >End Function

>> >I fully expect someone to now post something that works ten times faster
>> >using two lines of code, but this does work

>> It can be done in one line :-)

>> Public Function GeometricMean(strTable As String, strField As String)
>> As Double

>> GeometricMean = Exp(DAvg("Log(" & strField & ")", strTable))

>> End Function

>> Note that the numbers must be strictly positive. Also, DAvg
>> automatically disregards Null values, so there is no need for a
>> condition that excludes Null values.

>> Greetings
>> Matthias Kl?y
>> --
>> www.kcc.ch

>OK, so it can be done in one line, but mine's faster! (Just don't use it
>with lot's of big numbers or you'll get an overflow error - probably best to
>average the log whichever method is used).

I have to disagree on the speed issue, Andrew. It did run a test,
calculating the geometric mean of 1000 numbers  10 times with
different data each time. My function used 14 milliseconds in the
average, whereas your function took 29 milliseconds.
It is almost always faster to use a domain aggregate function than to
run through a recordset. The domain aggregate functions have a bad
reputation of being "slow" (probabliy stemming back to Access 1.x or
2.0). This reputation is not justified.

Greetings, Matthias Kl?y
--
www.kcc.ch



Fri, 16 Dec 2005 19:37:24 GMT  
 Geometric Mean?


Quote:
> On Fri, 27 Jun 2003 19:24:16 +0100, "Andrew Smith"



> >> On Thu, 26 Jun 2003 22:26:00 +0100, "Andrew Smith"



> >> >> I'm trying to calculate the geometric mean for a
> >> >> particular field in my database and can't find the
> >> >> function anywhere.  Does anyone have any
> >> >> modules/code/suggestions?

> >> >I've written a function to do this:

> >> >Public Function GeometricMean(strTable As String, strField As String)
As
> >> >Double

> >> >Dim db As DAO.Database
> >> >Dim rst As DAO.Recordset
> >> >Dim strSQL As String
> >> >Dim lngCount As Long
> >> >Dim dblProduct As Double

> >> >On Error GoTo HandleErrors

> >> >strSQL = "SELECT " & strField & " FROM " & strTable
> >> >strSQL = strSQL & " WHERE Not IsNull([" & strField & "]);"

> >> >Set db = CurrentDb
> >> >Set rst = db.OpenRecordset(strSQL, dbOpenForwardOnly)

> >> >With rst
> >> >    dblProduct = 1
> >> >    Do Until .EOF
> >> >        dblProduct = dblProduct * rst(0)
> >> >        lngCount = lngCount + 1
> >> >        .MoveNext
> >> >    Loop
> >> >    .Close
> >> >End With

> >> >GeometricMean = dblProduct ^ (1 / lngCount)

> >> >ExitHere:
> >> >    On Error Resume Next
> >> >    Set rst = Nothing
> >> >    db.Close
> >> >    Set db = Nothing
> >> >    Exit Function

> >> >HandleErrors:
> >> >    Select Case Err.Number
> >> >        Case Else
> >> >            MsgBox "Unexpected error calculating geometric mean" &
vbCr &
> >> >Err.Description & " (" & Err.Number & ")"
> >> >    End Select
> >> >    Resume ExitHere

> >> >End Function

> >> >I fully expect someone to now post something that works ten times
faster
> >> >using two lines of code, but this does work

> >> It can be done in one line :-)

> >> Public Function GeometricMean(strTable As String, strField As String)
> >> As Double

> >> GeometricMean = Exp(DAvg("Log(" & strField & ")", strTable))

> >> End Function

> >> Note that the numbers must be strictly positive. Also, DAvg
> >> automatically disregards Null values, so there is no need for a
> >> condition that excludes Null values.

> >> Greetings
> >> Matthias Kl?y
> >> --
> >> www.kcc.ch

> >OK, so it can be done in one line, but mine's faster! (Just don't use it
> >with lot's of big numbers or you'll get an overflow error - probably best
to
> >average the log whichever method is used).

> I have to disagree on the speed issue, Andrew. It did run a test,
> calculating the geometric mean of 1000 numbers  10 times with
> different data each time. My function used 14 milliseconds in the
> average, whereas your function took 29 milliseconds.
> It is almost always faster to use a domain aggregate function than to
> run through a recordset. The domain aggregate functions have a bad
> reputation of being "slow" (probabliy stemming back to Access 1.x or
> 2.0). This reputation is not justified.

> Greetings, Matthias Kl?y
> --
> www.kcc.ch

I did the same thing and got almost exactly the opposite results to you! I
must admit that I was a bit surprised by this. I followed a discussion about
domain aggregate functions on the newsgroup a few weeks ago. Someone posted
some supposedly faster functions that worked by walking through a recordset,
but I found that they were actually slower than than the original domain
aggregate functions.

Anyway, your function is certainly a neater way of doing it than mine.

Andrew



Sat, 17 Dec 2005 04:15:16 GMT  
 
 [ 6 post ] 

 Relevant Pages 

1. How can I calculate median and geometric mean using SQL???

2. Geometric shape using loops

3. Need geometric shapes code

4. geometric objects as command buttons

5. Need API beginner's tutorial for drawing geometric shapes

6. What does this error mean?

7. SQL Statement What does it mean?

8. Meaning of error messages

9. What does this mean?

10. Not sure what this error means. (Error 3426)

11. how to add a new dataset from excel to access by means of vba

12. The Back Admin means zero administration!!!

 

 
Powered by phpBB® Forum Software