I need to average the results in up to 4 fields. There will be times, however, when one or more of the fields will have no result. Following a suggestion from one of the tech forums, I set up the following calculation:

Case(

(AV_1a + AV_1b + AV_1c + AV_1d) = 0, 0,

(AV_1a + AV_1b + AV_1c + AV_1d) /

((not IsEmpty(AV_1a)) + (not IsEmpty(AV_1b))) + (not IsEmpty(AV_1c)))) + (not IsEmpty(AV_1d)))))

)

I just checked one average of 2 results (3.69 + 3.16). Instead of getting 3.43, the calulation is giving me 3.18. What am I doing wrong???

quote:

---

Originally posted by tmac:

I need to average the results in up to 4 fields. There will be times, however, when one or more of the fields will have no result. Following a suggestion from one of the tech forums, I set up the following calculation:

Case(

(AV_1a + AV_1b + AV_1c + AV_1d) = 0, 0,

(AV_1a + AV_1b + AV_1c + AV_1d) /

((not IsEmpty(AV_1a)) + (not IsEmpty(AV_1b))) + (not IsEmpty(AV_1c)))) + (not IsEmpty(AV_1d)))))

)

I just checked one average of 2 results (3.69 + 3.16). Instead of getting 3.43, the calulation is giving me 3.18. What am I doing wrong???

---

As far as I can tell, the calculation is correct. I would try breaking it out to see if each part is working correctly.

Create a calculation number field called perhaps "Divisor" and make it equal to ((not IsEmpty(AV_1a)) + (not IsEmpty(AV_1b))) + (not IsEmpty(AV_1c)))) + (not IsEmpty(AV_1d)). Make sure that for the record you mentioned, this field is equal to 2.

Create another calculation number field called "Total" and make it equal to (AV_1a + AV_1b + AV_1c + AV_1d) and make sure that for the record you mentioned, it is equal to 6.86.

If both of these test field work out to have the right values, try editing your original equation to equal Case(

Total = 0, 0,

Total / Divisor

)

Chuck