Calculating the avergae of 3 feilds

[canberry]

Hi all, need some help calculating the average of 5 feilds . But i want it to calculate the average based on feilds greater than zero.

EG. In a table u have feild Name stax btax ctax
Tom 50 55 31
Jill 100 100

Therfore The avergae of the ABC would be C&W (50+55+31)/3
Claro (100+100)/2

 

[Rabbie]

And your question is?

 

[canberry]

My 1st sentence. I want to know how to be able to accomplish what i have in the example given. I only know how to calculate average on feilds ragardless if any contains a null value. So the query will divide by the amount of feilds greater than 0

 

[Rabbie]

With your data looking unnormalised and an indeterminate number of fields this is going to be difficult to do in a query.

Perhaps you should look at your data structure first and make sure you have everything normalised.

 

[canberry]

There will always exist 3 feilds to carry out an average. Its just that somtimes, there exists a null value for a particular feild. And i do not want to include this figure as part of the average.. Any ideas ?

 

[Xx_TownDawg_xX]

Are you saying that when there is a NULL value, (in your second example) that your expression returns 200/3?.. instead of 200/2?

 

[canberry]

Correct. But i want it to divide by the amount of figures that are > 0. So it should really be divided by 2.

 

[Xx_TownDawg_xX]

Have you incorporated the Nz() function into your expression?

 

[GaryPanic]

i would have 3 extra hidden boxes (this might not be the best way ) and if a field is null or <1 then have the hidden field as a zero if greater than 1 then ahve the hidden box as a 1

 

[Xx_TownDawg_xX]

Re: Calculating the average of 3 fields

Avg: (IIf(IsNull([Val1]),0,[Val1])+IIf(IsNull([Val2]),0,[Val2])+IIf(IsNull([Val3]),0,[Val3]))/(IIf(IsNull([Val1]),0,1)+IIf(IsNull([Val2]),0,1)+IIf(IsNull([Val3]),0,1))

SELECT tblMath.ID, tblMath.Val1, tblMath.Val2, tblMath.Val3, (IIf(IsNull([Val1]),0,[Val1])+IIf(IsNull([Val2]),0,[Val2])+IIf(IsNull([Val3]),0,[Val3]))/(IIf(IsNull([Val1]),0,1)+IIf(IsNull([Val2]),0,1)+IIf(IsNull([Val3]),0,1)) AS [Avg]
FROM tblMath;

 

[canberry]

I tried this below but i still gettin the same results. Its taking the average of all three including the zero. I am goin to try the Zn function

Average: (IIf(IsNull([TableAvergae]![A]),0,[TableAvergae]![A])+IIf(IsNull([TableAvergae]!),0,[TableAvergae]!)+IIf(IsNull([TableAvergae]![C]),0,[TableAvergae]![C]))/(IIf(IsNull([TableAvergae]![A]),0,1)+IIf(IsNull([TableAvergae]!),0,1)+IIf(IsNull([TableAvergae]![C]),0,1))

 

[Xx_TownDawg_xX]

Re: Calculating the average of 3 fields

Can you post a sample of your db? Something is wrong, because as you can see -- the expression I posted (with screenshot) worked fine.

 

[canberry]

Town Dog, did you get a average of 100 for Jill when u ran the query ?

 

[Xx_TownDawg_xX]

The screenshot is in the thread. Take a look for yourself. I used your same exact data.

I will upload the database even.

By the way, that's TownDawg, not Town Dog.

 

[canberry]

It actually did work when i deleted the zero from the feild that was null . The set of data im working on contains alot of records which means there will be alot of zeros. Can this be coded to not treat the zero as part of the calculation ??

 

[Xx_TownDawg_xX]

... just that sometimes, there exists a null value for a particular field.

You never told us that you had zeroes, you said you had null.

Change the denominator "(IsNull([Val1])" to something like " ([Val1]>0)"

 

[Xx_TownDawg_xX]

Change the denominator "(IsNull([Val1])" to something like " ([Val1]>0)"

Avg: (IIf(IsNull([Val1]),0,[Val1])+IIf(IsNull([Val2]),0,[Val2])+IIf(IsNull([Val3]),0,[Val3]))/(IIf([Val1]>0,1,0)+IIf([Val2]>0,1,0)+IIf([Val3]>0,1,0))

 

[Brianwarnock]

Surely 0 is a valid number and Towndawg's original code is correct, if however you have a mixture of Null and 0 both of which you want to ignore then you must use something like (IIf(Nz([Val1],0)>0,1,0)+
inorder to check for either.

Please be specific in what can or cannot be true.

Brian

 

[canberry]

Re: Calculating the avergae of 3 feilds (Answered)

Thanks a Mil TownDawg. You da man Works just fine.

 

[Xx_TownDawg_xX]

Good deal. (It's good to be helping someone instead of asking for help.)

Glad it worked out..


.. (Now if we can just teach you how to spell.. )..