Jump to content

Averaging Accurately


This topic is 8596 days old. Please don't post here. Open a new topic instead.

Recommended Posts

I have 4 number fields formatted to 2 places as decimals. I want an average of these 4 fields. The trouble is not all records will have 4 entries. Some records could have as few as 2. I need it to be able to total whatever is available (2, 3 or 4 field totals) and then average using the appropriate divisor. Can it be done using calc fields or will I need to script it?

Link to comment
Share on other sites

First of all, be aware that numbers in FM are stored with the same precision independent of how you choose to display them.

You can create a calculation that averages correctly without using a script. Here is one way.

Average =

Case(

(Field1 + Field2 + Field3 + Field4) = 0, 0,

(Field1 + Field2 + Field3 + Field4) /

((not IsEmpty(Field1)) + (not IsEmpty(Field2)) + (not IsEmpty(Field3)) + (not IsEmpty(Field4)))

)

The test for a sum of zero will protect against dividing by zero when all fields are empty. Don't try to eliminate the extra parenthesis around the "not IsEmpty()", they are necessary for the proper operation of the calculation (no, I don't know why).

-bd

Link to comment
Share on other sites

quote:

Originally posted by LiveOak:

...

((not IsEmpty(Field1)) + (not IsEmpty(Field2)) + (not IsEmpty(Field3)) + (not IsEmpty(Field4))

...

Don't try to eliminate the extra parenthesis around the "not IsEmpty()", they are necessary for the proper operation of the calculation (no, I don't know why).

-bd

If you're interested in the reason why, it's because numerical addition has a higher priority in equaltions than boolean logic. The "not" applies to the expression in front of it, whatever that expression is. So without the parins, to figure out what the result would be, you would need to work from the right.

Say all the fields have data. Then the fourth IsEmpty will result in 1, and not 1 is 0. Add to this the third IsEmpty, resulting in 1, but not 1 is zero, and this works on down the line. So you get 0 instead of 4.

Chuck

Link to comment
Share on other sites

This topic is 8596 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 account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.