Averaging Accurately

[tmac]

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?

[LiveOak]

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

[Chuck]

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

[LiveOak]

Thanks, good point. I lost sight of the fact that prefix operations follow the same rules of precedence as infix operations! -bd

[This message has been edited by LiveOak (edited September 14, 2000).]