|
 |
access thread: Modifying GeoMean formula for Access
Message #1 by J.R. Pearson <okie_jr@g...> on Wed, 26 Feb 2003 11:35:13 -0600
|
|
I copied the following formula that I found in the archives to be able
to run Geometric Averages in Access 97 (my job slow about getting us
updates). It is working great except I need to make a modification in
the formula. I need to modify the formula to handle zeros in the
data. Presently, any zero in the data will result in an error.
Thanks,
J.R.
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
Message #2 by "Charlie Goodwin" <cgoodwin@c...> on Wed, 26 Feb 2003 14:13:57 -0500
|
|
Hi,
Howzabout??? Maybe this is too simple?
Private sub TestForZeroInGMean3()
'kill it if there's a zero in the way
If [YourTroublesomeDataNameHere] =0 then ' test
MsgBox"your data missing messager" ' inform if you want
Exit Sub ' get out
Else ' if it's cool run your function
GMean3
End if
End sub
Repeat similar for all data at issue?
Charlie
>
> I copied the following formula that I found in the archives to be able
> to run Geometric Averages in Access 97 (my job slow about getting us
> updates). It is working great except I need to make a modification in
> the formula. I need to modify the formula to handle zeros in the
> data. Presently, any zero in the data will result in an error.
>
> Thanks,
> J.R.
>
>
> 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
>
>
Message #3 by Beth Moffitt <BethMoffitt@i...> on Wed, 26 Feb 2003 13:17:44 -0600
|
|
JR,
Check out the NZ function. Straight from the help files:
Nz Function
See Also Applies To Example Specifics
You can use the Nz function to return zero, a zero-length string (" "), or
another specified value when a Variant is Null. Variant.
expression.Nz(Value, ValueIfNull)
expression Required. An expression that returns one of the objects in the
Applies To list.
Value Required Variant. A variable of data type Variant.
ValueIfNull Optional Variant. Optional (unless used in a query). A Variant
that supplies a value to be returned if the variant argument is Null. This
argument enables you to return a value other than zero or a zero-length
string. Note If you use the Nz function in an expression in a query
without using the valueifnull argument, the results will be a zero-length
string in the fields that contain null values.
Remarks
For example, you can use this function to convert a Null value to another
value and prevent it from propagating through an expression.
If the value of the variant argument is Null, the Nz function returns the
number zero or a zero-length string (always returns a zero-length string
when used in a query expression), depending on whether the context indicates
the value should be a number or a string. If the optional valueifnull
argument is included, then the Nz function will return the value specified
by that argument if the variant argument is Null. When used in a query
expression, the NZ function should always include the valueifnull argument,
If the value of variant isn't Null, then the Nz function returns the value
of variant.
The Nz function is useful for expressions that may include Null values. To
force an expression to evaluate to a non-Null value even when it contains a
Null value, use the Nz function to return a zero, a zero-length string, or a
custom return value.
For example, the expression 2 + varX will always return a Null value when
the Variant varX is Null. However, 2 + Nz(varX) returns 2.
You can often use the Nz function as an alternative to the IIf function. For
example, in the following code, two expressions including the IIf function
are necessary to return the desired result. The first expression including
the IIf function is used to check the value of a variable and convert it to
zero if it is Null.
varTemp = IIf(IsNull(varFreight), 0, varFreight)
varResult = IIf(varTemp > 50, "High", "Low")
In the next example, the Nz function provides the same functionality as the
first expression, and the desired result is achieved in one step rather than
two.
varResult = IIf(Nz(varFreight) > 50, "High", "Low")
If you supply a value for the optional argument valueifnull, that value will
be returned when variant is Null. By including this optional argument, you
may be able to avoid the use of an expression containing the IIf function.
For example, the following expression uses the IIf function to return a
string if the value of varFreight is Null.
varResult = IIf(IsNull(varFreight), _
"No Freight Charge", varFreight)
In the next example, the optional argument supplied to the Nz function
provides the string to be returned if varFreight is Null.
varResult = Nz(varFreight, "No Freight Charge")
hth,
Beth
-----Original Message-----
From: J.R. Pearson [mailto:okie_jr@g...]
Sent: Wednesday, February 26, 2003 11:35 AM
To: Access
Subject: [access] Modifying GeoMean formula for Access
I copied the following formula that I found in the archives to be able
to run Geometric Averages in Access 97 (my job slow about getting us
updates). It is working great except I need to make a modification in
the formula. I need to modify the formula to handle zeros in the
data. Presently, any zero in the data will result in an error.
Thanks,
J.R.
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
Message #4 by "Hamilton. Tom" <hamiltont@s...> on Wed, 26 Feb 2003 11:39:39 -0800
|
|
Hi JR and Beth,
While nz is a great time and code saver, it is the 0 that's the problem,
as in 'divide by zero'...which appears to be the nature of JR's
application.
Probably best to test for Dividend=3D0 or Divisor=3D0 and set then
Result=3D0 (or some other value).
While NZ can test the divisor and/or the dividend, it can't be used to
set the result by itself.
NZ is a great function, little known, but a real big help with many
applications.
-----Original Message-----
From: Beth Moffitt [mailto:BethMoffitt@i...]
Sent: Wednesday, February 26, 2003 11:18 AM
To: Access
Subject: [access] Modifying GeoMean formula for Access
JR,
Check out the NZ function. Straight from the help files:
Nz Function
See Also Applies To Example Specifics
You can use the Nz function to return zero, a zero-length string (" "),
or
another specified value when a Variant is Null. Variant.
expression.Nz(Value, ValueIfNull)
expression Required. An expression that returns one of the objects in
the
Applies To list.
Value Required Variant. A variable of data type Variant.
ValueIfNull Optional Variant. Optional (unless used in a query). A
Variant
that supplies a value to be returned if the variant argument is Null.
This
argument enables you to return a value other than zero or a zero-length
string. Note If you use the Nz function in an expression in a query
without using the valueifnull argument, the results will be a
zero-length
string in the fields that contain null values.
Remarks
For example, you can use this function to convert a Null value to
another
value and prevent it from propagating through an expression.
If the value of the variant argument is Null, the Nz function returns
the
number zero or a zero-length string (always returns a zero-length string
when used in a query expression), depending on whether the context
indicates
the value should be a number or a string. If the optional valueifnull
argument is included, then the Nz function will return the value
specified
by that argument if the variant argument is Null. When used in a query
expression, the NZ function should always include the valueifnull
argument,
If the value of variant isn't Null, then the Nz function returns the
value
of variant.
The Nz function is useful for expressions that may include Null values.
To
force an expression to evaluate to a non-Null value even when it
contains a
Null value, use the Nz function to return a zero, a zero-length string,
or a
custom return value.
For example, the expression 2 + varX will always return a Null value
when
the Variant varX is Null. However, 2 + Nz(varX) returns 2.
You can often use the Nz function as an alternative to the IIf function.
For
example, in the following code, two expressions including the IIf
function
are necessary to return the desired result. The first expression
including
the IIf function is used to check the value of a variable and convert it
to
zero if it is Null.
varTemp =3D IIf(IsNull(varFreight), 0, varFreight)
varResult =3D IIf(varTemp > 50, "High", "Low")
In the next example, the Nz function provides the same functionality as
the
first expression, and the desired result is achieved in one step rather
than
two.
varResult =3D IIf(Nz(varFreight) > 50, "High", "Low")
If you supply a value for the optional argument valueifnull, that value
will
be returned when variant is Null. By including this optional argument,
you
may be able to avoid the use of an expression containing the IIf
function.
For example, the following expression uses the IIf function to return a
string if the value of varFreight is Null.
varResult =3D IIf(IsNull(varFreight), _
"No Freight Charge", varFreight)
In the next example, the optional argument supplied to the Nz function
provides the string to be returned if varFreight is Null.
varResult =3D Nz(varFreight, "No Freight Charge")
hth,
Beth
-----Original Message-----
From: J.R. Pearson [mailto:okie_jr@g...]
Sent: Wednesday, February 26, 2003 11:35 AM
To: Access
Subject: [access] Modifying GeoMean formula for Access
I copied the following formula that I found in the archives to be able
to run Geometric Averages in Access 97 (my job slow about getting us
updates). It is working great except I need to make a modification in
the formula. I need to modify the formula to handle zeros in the
data. Presently, any zero in the data will result in an error.
Thanks,
J.R.
Public Function GMean3(ParamArray intArgs() As Variant) As Double
Dim dblReturn As Double
Dim intN As Integer
Dim i As Integer
dblReturn =3D 0
intN =3D 0
' Multiply the args together
For i =3D LBound(intArgs) To UBound(intArgs)
If IsNumeric(intArgs(i)) Then
intN =3D intN + 1
If i =3D LBound(intArgs) Then
dblReturn =3D CDbl(intArgs(i))
Else
dblReturn =3D 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 =3D dblReturn ^ (1 / intN)
Else
dblReturn =3D 0
End If
GMean3 =3D dblReturn
End Function
Message #5 by "J. R. Pearson" <okie_jr@g...> on Thu, 27 Feb 2003 00:28:21
|
|
Unfortunately, I have to use the number. I am considering converting it
to a very low number approaching zero, before running the calculation.
Tom and Beth gave some ideas for converting the number, but I am not sure
if there is a way to include it in the GMean3 function below. There are
other ways around the problem, but it would work much more smoothly if I
could include it in one function.
--J.R.
> Hi,
Howzabout??? Maybe this is too simple?
Private sub TestForZeroInGMean3()
'kill it if there's a zero in the way
If [YourTroublesomeDataNameHere] =0 then ' test
MsgBox"your data missing messager" ' inform if you want
Exit Sub ' get out
Else ' if it's cool
run your function
GMean3
End if
End sub
Repeat similar for all data at issue?
Charlie
>
> I copied the following formula that I found in the archives to be able
> to run Geometric Averages in Access 97 (my job slow about getting us
> updates). It is working great except I need to make a modification in
> the formula. I need to modify the formula to handle zeros in the
> data. Presently, any zero in the data will result in an error.
>
> Thanks,
> J.R.
>
>
> 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
>
>
Message #6 by "Charlie Goodwin" <cgoodwin@c...> on Wed, 26 Feb 2003 19:28:54 -0500
|
|
Maybe something more like this???
Private sub TestForZeroInGMean3()
Dat = YourTroublesomeDataItemName
SmallNum = .0000001 ' or some other suitable small number
' which you can assign as needed for the
' math to work
If Dat =0 then ' test for a problem
Dat = SmallNum ' make the replacement
MsgBox"Altered data message here if needed" ' inform if you need
YourTroublesomeDataItemName =Dat
GMean3
Else
GMean3 ' run as is if no problem
End if
End sub
> Unfortunately, I have to use the number. I am considering converting it
> to a very low number approaching zero, before running the calculation.
> Tom and Beth gave some ideas for converting the number, but I am not sure
> if there is a way to include it in the GMean3 function below. There are
> other ways around the problem, but it would work much more smoothly if I
> could include it in one function.
>
> --J.R.
>
>
> > Hi,
>
> Howzabout??? Maybe this is too simple?
>
> Private sub TestForZeroInGMean3()
> 'kill it if there's a zero in the way
> If [YourTroublesomeDataNameHere] =0 then ' test
> MsgBox"your data missing messager" ' inform if you want
> Exit Sub ' get out
> Else ' if it's cool
> run your function
> GMean3
> End if
> End sub
>
> Repeat similar for all data at issue?
>
> Charlie
>
>
>
>
>
>
>
> >
> > I copied the following formula that I found in the archives to be able
> > to run Geometric Averages in Access 97 (my job slow about getting us
> > updates). It is working great except I need to make a modification in
> > the formula. I need to modify the formula to handle zeros in the
> > data. Presently, any zero in the data will result in an error.
> >
> > Thanks,
> > J.R.
> >
> >
> > 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
> >
> >
|
|
|
View Cart
