Skip to main content
Topics#Data ManagementSalesforce Bot's Question
Salesforce Bot (Endeavor) asked in #Data Management
Oct 23, 2015, 3:52 PM

Formula #Error! when CAGR rate is left blank

When companies are selected to be in our portfolio we track how thier CAGR rates increase. Currently the formula doesn't allow for that when there is no information Revenue_USD_At_Selection__C so it Error's out.

 

Here's the Current Formula:

IF(
    OR(
        (YEAR(TODAY()) - YEAR(Application_Decision_Date__c)) = 1,
        ISBLANK(Revenue_USD_At_Selection__c),
        Revenue_USD_At_Selection__c = 0,
        (CAGR_Last_Year_Rev_USD__c / Revenue_USD_At_Selection__c) = 0
    ),
    NULL,
    EXP(
        (
            1 / ((YEAR(TODAY()) - YEAR(Application_Decision_Date__c)) - 1)
        ) * 
        LN(CAGR_Last_Year_Rev_USD__c / Revenue_USD_At_Selection__c) 
    ) - 1
)

 

Formula #Error! when CAGR rate is left blank

 

Thoughts?
7 answers
  1. Brian Makas (N/A)
    Oct 23, 2015, 4:10 PM
    How about just wrapping the whole thing in one more "IF"

     

    IF(

     

    OR(ISBLANK(Revenue_USD_At_Selection__c), Revenue_USD_At_Selection__c = 0), NULL, 

     

    IF( OR( (YEAR(TODAY()) - YEAR(Application_Decision_Date__c)) = 1, ISBLANK(Revenue_USD_At_Selection__c), Revenue_USD_At_Selection__c = 0, (CAGR_Last_Year_Rev_USD__c / Revenue_USD_At_Selection__c) = 0 ), NULL, EXP( ( 1 / ((YEAR(TODAY()) - YEAR(Application_Decision_Date__c)) - 1) ) * LN(CAGR_Last_Year_Rev_USD__c / Revenue_USD_At_Selection__c) ) - 1 )

     

    )
Write an answer...
0/9000