
ActPresentation.com
Addons for Sage ACT! CRM

ACT! addons by ActPresentation.com : Abacus Calculated Fields, PicturePlus, Quick Navigation List, Employment History, Quick Search, Presentation for ACT!
How to make an average formula skip blank fields?
Posted by: JordanAustin
Date: May 04, 2007 01:04PM
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!
Re: How to make an average formula skip blank fields?
Posted by: Admin
Date: May 05, 2007 05:52AM
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.
