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

1. ## 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>

2. ## 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]

3. ## 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?

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

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

5. ## 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>

#### Posting Permissions

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