Skip to main content
Topics#Data ManagementEdward Castro's Question
Edward Castro (NDS) asked in #Data Management
Oct 29, 2010, 8:48 PM

Geting ERROR! When denominator or numerator are 0 in Formula

I am bringing in actual sales numbers in our accounts, YTD and LYTD.  I am then creating a formula field that will show me the % in difference.  Ex.  YTD=$9,000 and LYTD is $10,000, so the with the following calculation I can say sales are down 10% compare to last year at this time.  The problem is that if the LYDT or YTD = 0 (meaning there were no sales last year, or maybe none this year), I get an #.ERROR! displayed in the field.  How can I write this formula so it does not give the the error? Also if LYTD is 0 and YTD is let's say $10,000, could we have it display a positive #, or is there a need to have a value in this instance?

 

(Account.YTDSales__c/Account.LYTDSales__c)-1

 

Thanks,

 

Grateful
10 answers
  1. Edward Castro (NDS)
    Nov 1, 2010, 8:02 PM
    The final formula that resolved the issue is as follows:

     

    IF(and(YTDSales__c>0, LYTDSales__c>0),  (YTDSales__c / LYTDSales__c)-1, 0)

     

    thanks,

     

    Grateful ED
Write an answer...
0/9000