Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Northern, NJ, New Jersey, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Avg 3 fields in Query (2000 SR-1)

    I have Gross Adds for [Oct 2001] [Nov 2001] and [Dec 2001] in a table. In a query column I need to avg the three together.
    I tried this:
    field:4Q2000GA: Avg(([Sep 2000]+[Oct 2000]+[Dec 2000]))
    total:Expression
    and they are adding and NOT averaging

    How can I avg the three in a query field <img src=/S/hairy.gif border=0 alt=hairy width=15 height=15>
    There is always a way.

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Avg 3 fields in Query (2000 SR-1)

    Avg is a function to use over records, not over fields.
    You'll have to use
    field:4Q2000GA[Sep 2000]+[Oct 2000]+[Dec 2000])/3

    PS: In your mail you have field [Oct 2001] [Nov 2001] and [Dec 2001]
    In the expression you have [Sep 2000]+[Oct 2000]+[Dec 2000]
    Francois

  3. #3
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Northern, NJ, New Jersey, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Avg 3 fields in Query (2000 SR-1)

    <big>That works!</big>

    I am using this 2X (same expression for 2001, too) and then I am dividing [4Q2000GA]/[4Q2001GA] for a "4Q Year over Year" performance percent. There are certain locations that were not opened in 2000 and are returning '#Error' as there is nothing to divide.

    Another Question:
    Can I change the formula:
    YOY: [4Q2001GA]/[4Q2000GA]
    to allow it to return a zero where there is no 2000 number to divide rather than the error?
    There is always a way.

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Avg 3 fields in Query (2000 SR-1)

    Try this:
    YOY: IIf(IsNull([4Q2000GA]) Or [4Q2000GA] =0,0,[4Q2001GA]/[4Q2000GA] )
    Francois

  5. #5
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Northern, NJ, New Jersey, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Avg 3 fields in Query (2000 SR-1)

    Once again, I am in your debt!

    Thanks for the help - <font face="Comic Sans MS">Calvin can keep on dancin'</font face=comic>
    There is always a way.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •