Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

AVG formula

Discussion in 'Information Technology' started by Risikio, Nov 10, 2005.

Tags:
  1. Risikio

    Risikio
    Expand Collapse
    Guest

    I want to determine the AVG of a field without including any "0" fields. I
    can not leave these fields blank, because in another area I use the AVG with
    the "0" fields.

    My formula is =AVG([fieldA]>0) however, the AVG it returns can not be
    correct because all of the non "0" values are positive, and the value
    returned is -0.4.

    Any help would be greatly appreciated.

    Thanks.
     
  2. Loading...

    Similar Threads Forum Date
    Nature Indian Bison to Roam Bandhavgarh Reserve Again, After a Decade Breaking News Jan 25, 2011
    Controversial Sikh Vichar Manch Formula To Remove Maoists Hostilities within India Hard Talk Apr 27, 2010
    Sikh News Punjab formulates new education scheme (Express India) Breaking News Sep 8, 2008
    Sikh News Power-sharing formula in Punjab finalised (Gulf Times) Breaking News Apr 7, 2008
    Sikh News PPPP, PML-N mulling over coalition formula in Punjab (Pakistan News Tribune) Breaking News Mar 7, 2008

  3. KARL DEWEY

    KARL DEWEY
    Expand Collapse
    Guest

    Use <>0

    "Risikio" wrote:

    > I want to determine the AVG of a field without including any "0" fields. I
    > can not leave these fields blank, because in another area I use the AVG with
    > the "0" fields.
    >
    > My formula is =AVG([fieldA]>0) however, the AVG it returns can not be
    > correct because all of the non "0" values are positive, and the value
    > returned is -0.4.
    >
    > Any help would be greatly appreciated.
    >
    > Thanks.
     
  4. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Wed, 9 Nov 2005 08:49:07 -0800, "Risikio"
    <Risikio@discussions.microsoft.com> wrote:

    >I want to determine the AVG of a field without including any "0" fields. I
    >can not leave these fields blank, because in another area I use the AVG with
    >the "0" fields.
    >
    >My formula is =AVG([fieldA]>0) however, the AVG it returns can not be
    >correct because all of the non "0" values are positive, and the value
    >returned is -0.4.
    >
    >Any help would be greatly appreciated.
    >
    >Thanks.


    Well, zero is a number - if you were to want to average (-2, -1, 0, 1,
    2) you would want to get zero as the result!

    If you're calculating the two types of averages in different queries,
    perhaps you could simply use a criterion of

    >0


    on one of the queries to exclude zero values from the query
    altogether. If that's not feasible (i.e. you want to see those records
    but just not include them in the average), you'll need to do something
    snarky like:

    =DSum("[fieldname]", "[tablename]", "<criteria>") / DCount("*",
    "[tablename]", "<criteria> AND [fieldname] > 0")

    where the <criteria> select that subset of records which you wish to
    average.

    John W. Vinson[MVP]
     
  5. Risikio

    Risikio
    Expand Collapse
    Guest

    John,

    This is in a subreport. The subreport will show both averages. Averages
    with the "0" is shown for all of the records. Averages without the "0" is
    shown for only those records that reported a value greater than "0". Since
    this is in a subreport, I am guessing I would need to run the second query
    like you suggested, however that would create a subreport within a subreport.
    Is that possible, to put a subreport in another subreport?

    "John Vinson" wrote:

    > On Wed, 9 Nov 2005 08:49:07 -0800, "Risikio"
    > <Risikio@discussions.microsoft.com> wrote:
    >
    > >I want to determine the AVG of a field without including any "0" fields. I
    > >can not leave these fields blank, because in another area I use the AVG with
    > >the "0" fields.
    > >
    > >My formula is =AVG([fieldA]>0) however, the AVG it returns can not be
    > >correct because all of the non "0" values are positive, and the value
    > >returned is -0.4.
    > >
    > >Any help would be greatly appreciated.
    > >
    > >Thanks.

    >
    > Well, zero is a number - if you were to want to average (-2, -1, 0, 1,
    > 2) you would want to get zero as the result!
    >
    > If you're calculating the two types of averages in different queries,
    > perhaps you could simply use a criterion of
    >
    > >0

    >
    > on one of the queries to exclude zero values from the query
    > altogether. If that's not feasible (i.e. you want to see those records
    > but just not include them in the average), you'll need to do something
    > snarky like:
    >
    > =DSum("[fieldname]", "[tablename]", "<criteria>") / DCount("*",
    > "[tablename]", "<criteria> AND [fieldname] > 0")
    >
    > where the <criteria> select that subset of records which you wish to
    > average.
    >
    > John W. Vinson[MVP]
    >
     
  6. Risikio

    Risikio
    Expand Collapse
    Guest

    Karl,

    Unfortunately, this returns the same -0.4 value. The value with the "0"
    fields is 0.77, so the value without the "0" fields should be higher than
    that. I have even tried the following formula
    =Sum([fieldA]<>0)/Count([fieldA]<>0). Any other suggestions?

    Thanks.

    "KARL DEWEY" wrote:

    > Use <>0
    >
    > "Risikio" wrote:
    >
    > > I want to determine the AVG of a field without including any "0" fields. I
    > > can not leave these fields blank, because in another area I use the AVG with
    > > the "0" fields.
    > >
    > > My formula is =AVG([fieldA]>0) however, the AVG it returns can not be
    > > correct because all of the non "0" values are positive, and the value
    > > returned is -0.4.
    > >
    > > Any help would be greatly appreciated.
    > >
    > > Thanks.
     
  7. Risikio

    Risikio
    Expand Collapse
    Guest

    The following formula seems to work for my situation.

    =Sum([fieldA])/Sum(IIF([fieldA]>0,1,0))

    Thanks for your help.

    "John Vinson" wrote:

    > On Wed, 9 Nov 2005 08:49:07 -0800, "Risikio"
    > <Risikio@discussions.microsoft.com> wrote:
    >
    > >I want to determine the AVG of a field without including any "0" fields. I
    > >can not leave these fields blank, because in another area I use the AVG with
    > >the "0" fields.
    > >
    > >My formula is =AVG([fieldA]>0) however, the AVG it returns can not be
    > >correct because all of the non "0" values are positive, and the value
    > >returned is -0.4.
    > >
    > >Any help would be greatly appreciated.
    > >
    > >Thanks.

    >
    > Well, zero is a number - if you were to want to average (-2, -1, 0, 1,
    > 2) you would want to get zero as the result!
    >
    > If you're calculating the two types of averages in different queries,
    > perhaps you could simply use a criterion of
    >
    > >0

    >
    > on one of the queries to exclude zero values from the query
    > altogether. If that's not feasible (i.e. you want to see those records
    > but just not include them in the average), you'll need to do something
    > snarky like:
    >
    > =DSum("[fieldname]", "[tablename]", "<criteria>") / DCount("*",
    > "[tablename]", "<criteria> AND [fieldname] > 0")
    >
    > where the <criteria> select that subset of records which you wish to
    > average.
    >
    > John W. Vinson[MVP]
    >
     

Share This Page