ActPresentation.com HOME FAQ FORUM info@ActPresentation.com
Phorum: speed & power.

ActPresentation.com


Add-ons 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.

ACT! software CRM addons - best plugins for ACT contact management software ( Act! by Sage software : ACT!2005, ACT!2006, ACT!2007, ACT!2008, Act by Sage Premium for Workgroups ).
Custom addon solutions ( ACT! add ons ) development and CRM (Customer Relationship Management) software training and consulting.