Calculating average of average

[Lars Brownie]

In a report I have a calculated field that calculates the average of 3
fields:
=([Field1]+[Field2]+[Field3])/3
However the division factor should not always be 3. When for instance only
Field2 en Field3 have a value and Field1 has no value, the division factor
should be 2. How can I achieve this?

Thanks,
Lars

 

[Pieter Wijnen]

Try

(Nz(f1,0) + Nz(f2,0) + nz(f3,0)) / Abs((Not IsNull(f1)) + (Not IsNull(f2)) +
(Not IsNull(f3)))

HtH

Pieter

 

[Lars Brownie]

Thanks Pieter. Works great!

"Pieter Wijnen"

Try

(Nz(f1,0) + Nz(f2,0) + nz(f3,0)) / Abs((Not IsNull(f1)) + (Not IsNull(f2))
+ (Not IsNull(f3)))

HtH

Pieter

In a report I have a calculated field that calculates the average of 3
fields:
=([Field1]+[Field2]+[Field3])/3
However the division factor should not always be 3. When for instance
only Field2 en Field3 have a value and Field1 has no value, the division
factor should be 2. How can I achieve this?

Thanks,
Lars

 

[Pieter Wijnen]

crude, but..

P

Thanks Pieter. Works great!

"Pieter Wijnen"

Try

(Nz(f1,0) + Nz(f2,0) + nz(f3,0)) / Abs((Not IsNull(f1)) + (Not
IsNull(f2)) + (Not IsNull(f3)))

HtH

Pieter

In a report I have a calculated field that calculates the average of 3
fields:
=([Field1]+[Field2]+[Field3])/3
However the division factor should not always be 3. When for instance
only Field2 en Field3 have a value and Field1 has no value, the division
factor should be 2. How can I achieve this?

Thanks,
Lars