Re: Product Calculation in an Access Query
- From: "John Spencer" <spencer@xxxxxxxxx>
- Date: Mon, 29 Oct 2007 14:44:00 -0400
I have this note in my things to look at pile. I don't know if it will
work for you or not.
Per Brian Camire
If "value" contains only positive numbers(no nulls, no negatives, no zero
value), try:
Exp(Sum(Log([t2.value])))
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.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
"SherryW" <SherryW@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:79299AC1-4E34-4F77-8950-E0318D41620D@xxxxxxxxxxxxxxxx
Unfortunately, the values are not always positive as there are periods
where
negative returns have been realized.
--
SherryW
"Michel Walsh" wrote:
If the values are strictly positives, you can EXP(SUM(LOG( fieldName)))
to
get the same result as, in Excel, PRODUCT( column_range )
That comes from the mathematical fact that:
a^(b+c) == a^b * a^c
Vanderghast, Access MVP
"SherryW" <SherryW@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:75B412C7-51F1-4FE9-B0CF-EB4236E51944@xxxxxxxxxxxxxxxx
We have a table of Index Returns for every month end starting in 1992
to
current date. In access I am trying to calculate the Annual compound
return
for 10 yrs, 5 yrs, 3 yrs and 1 yr.
Originally this calculation was done in excel and each index would have
a
worksheet sheet with all the monthly values and a column called Return.
In
the column called return there is a formula as follows
=PRODUCT(I44:I163)^(1/10)-1 (for 10 yr)
=PRODUCT(I92:I163)^(1/5)-1 (for 5 yr)
=PRODUCT(I128:I163)^(1/3)-1 (for 3 yr
=PRODUCT(I152:I163)-1 (for 1 yr)
Every year, we would redefine the 10 yr, 5 yr 3yr and 1 yr range to
include
the proper dates for the formulas above.
My question is: how do I do this calculation in Access? There isn't a
product function. I have a table with all of the Monthly Index values
since
1992 to current date. The fields are:
VLDT, Index Code, Index Name and Return. So, for the 10 yr calculation
I
can put in a date range of ie: Between #31/12/2006# and #31/12/2006#,
I
would need to either group by Index Code OR Index Name but here's the
kicker,
it doesn't appear that there is a product function in access. So, any
pearls
of wisdom on how I would do this product calculation above? I am so
stumped
here!
--
SherryW
.
- References:
- Re: Product Calculation in an Access Query
- From: Michel Walsh
- Re: Product Calculation in an Access Query
- From: SherryW
- Re: Product Calculation in an Access Query
- Prev by Date: Re: Product Calculation in an Access Query
- Next by Date: Re: Cumulative number in query
- Previous by thread: Re: Product Calculation in an Access Query
- Next by thread: Re: Product Calculation in an Access Query
- Index(es):
Relevant Pages
|
