Average ()

[Andy]

Friends,
How do get Average of the values from 6 Different Fields fromsame row? [Not
the avg of rows from the same field}
Andy

 

[John W. Vinson]

Friends,
How do get Average of the values from 6 Different Fields fromsame row? [Not
the avg of rows from the same field}
Andy

If none of them will ever be NULL it's easy:

([Field1] + [Field2] + [Field3] + [Field4] + [Field5] + [Field6]) / 6

If you want to ignore nulls, i.e. the average of 1, 2, 3, NULL, NULL, NULL
should be 2:

(NZ([Field1]) + NZ([Field2]) + NZ([Field3]) + NZ([Field4]) + NZ([Field5]) +
NZ([Field6])) / (6+IsNull([Field1]) + IsNull([Field2]) + IsNull([Field3]) +
IsNull([Field4]) + IsNull([Field5]) + IsNull([Field6]))

Nasty implementation dependent code I confess, using the fact that IsNull
returns -1 if the field is null, 0 otherwise.

Might you need to normalize your repeating groups...?

John W. Vinson [MVP]

 

[Jeff Boyce]

?Send your data to Excel and use the spreadsheet functions?

Regards

Jeff Boyce
Microsoft Office/Access MVP