Calculating average of average

L

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
 
P

Pieter Wijnen

Try

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

HtH

Pieter
 
L

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

Lars Brownie said:
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
 
P

Pieter Wijnen

crude, but..

P

Lars Brownie said:
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

Lars Brownie said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top