Multiplying records in same field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How do I multiply a number of records in a field similarly to the way I would
use the PRODUCT funtion in excel? Thanks
 
Frank

I'm not really clear on what you are trying to do. Are you saying you have
more than one value in a field? If so, this violates the spirit, if not the
letter of good database design.

I suppose you could set a reference to the Excel object model and use the
Excel PRODUCT function in a function you create in Access...

Good luck

Jeff Boyce
<Access MVP>
 
Jeff,

I'm trying to calculate the Geometric mean for a number "n" of records. Each
record has a field "date" and a field "Return". I need to to calculate
(Return1*Return2*...Return(n))^(1/n)

Thanks.
 
Well if the total is small enough you can try


If "return" contains only positive numbers(no nulls, no negatives, no zero
value), try the following.

Exp(Sum(Log([Return])))^(1/Count([Return])

Otherwise, the following from an old post may give you a place to start.

Per Brian Camire

Otherwise, try using the following expression:

IIf(Sum(IIf([Your Field]<0,1,0)) Mod 2=1,-1,1)*Sgn(Min(Abs([YourField])))*Exp(Sum(Log(Abs(IIf([YourField]=0,1,[YourField]))))

For reference:

1. The expression "IIf(Sum(IIf([Your Field]<0,1,0)) Mod 2=1,-1,1)"
evaluates to -1 if there are an odd number of negative values, or 1
otherwise.

2. The expression "Sgn(Min(Abs([Your Field])))" evaluates to 0 if one of
the values is zero, or 1 otherwise.

3. The expression "Exp(Sum(Log(Abs(IIf([Your Field]=0,1,[Your Field]))))"
returns the product of the absolute values, substituting values of 0 with 1.
Substituting values of 0 with 1 is simply a "trick" to avoid the "Invalid
procedure call error". If there is a value of zero, the result of this
expression doesn't matter anyway, since it will be multiplied by the result
of the second expression, which will be zero.
 
Thanks John, your sugestion sorted it out.

Frank

John Spencer (MVP) said:
Well if the total is small enough you can try


If "return" contains only positive numbers(no nulls, no negatives, no zero
value), try the following.

Exp(Sum(Log([Return])))^(1/Count([Return])

Otherwise, the following from an old post may give you a place to start.

Per Brian Camire

Otherwise, try using the following expression:

IIf(Sum(IIf([Your Field]<0,1,0)) Mod 2=1,-1,1)*Sgn(Min(Abs([YourField])))*Exp(Sum(Log(Abs(IIf([YourField]=0,1,[YourField]))))

For reference:

1. The expression "IIf(Sum(IIf([Your Field]<0,1,0)) Mod 2=1,-1,1)"
evaluates to -1 if there are an odd number of negative values, or 1
otherwise.

2. The expression "Sgn(Min(Abs([Your Field])))" evaluates to 0 if one of
the values is zero, or 1 otherwise.

3. The expression "Exp(Sum(Log(Abs(IIf([Your Field]=0,1,[Your Field]))))"
returns the product of the absolute values, substituting values of 0 with 1.
Substituting values of 0 with 1 is simply a "trick" to avoid the "Invalid
procedure call error". If there is a value of zero, the result of this
expression doesn't matter anyway, since it will be multiplied by the result
of the second expression, which will be zero.

Jeff,

I'm trying to calculate the Geometric mean for a number "n" of records. Each
record has a field "date" and a field "Return". I need to to calculate
(Return1*Return2*...Return(n))^(1/n)

Thanks.
Click to expand...
Click to expand...
 
Back
Top