tmac Posted September 15, 2000 Share Posted September 15, 2000 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??? Link to comment Share on other sites More sharing options...
Chuck Posted September 20, 2000 Share Posted September 20, 2000 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 Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 8810 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now