Hi,
I am calculating 4 fields and placing the average of them into a 5th field. My problem I'm having is not ALL the fields will have a have a value sometimes. This is affecting my average in the 5th field because it is counting the blank fields as 0's (Zero), is there a way i can make the average skip over any field's that are blank or Zero?

Thanks for any help!

Jordan,
below is formula example for 3 fields. This formula will skip blank fileds.

([Field 1] + [Field 2] + [Field 3])  /  (   (IF [Field 1]=0 THEN 0 ELSE 1 END) + (IF [Field 2]=0 THEN 0 ELSE 1 END) + (IF [Field 3]=0 THEN 0 ELSE 1 END)  )

Now I will try to explain this formula :

Original formula ( that calculates average of 3 fields ) was :

( [Field 1] + [Field 2] + [Field 3] ) / 3

Now we change it a littke bit :

( [Field 1] + [Field 2] + [Field 3] ) / (1+1+1)

Now we want to make this formula to skip blank and zero fileds - we need to replace every 1 in denominator with condition :

(IF [Field N]=0 THEN 0 ELSE 1 END)

so resulting formula is :

([Field 1] + [Field 2] + [Field 3]) / ( (IF [Field 1]=0 THEN 0 ELSE 1 END) + (IF [Field 2]=0 THEN 0 ELSE 1 END) + (IF [Field 3]=0 THEN 0 ELSE 1 END) )



Edited 1 time(s). Last edit at 05/22/2007 03:47PM by Admin.