function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
JaxBeachJaxBeach 

How do you have #Error! not show up in a formula result and replace it with 0?

Hi,

 

I have a simple formual that calculates sales growth percent.

 

Current YTD Sales Annualized / Current Year Goal  or

 

Current_TYD_Sales_Annualized__c / Current_Year_Goal__c

 

how can I have it return a result of 0 instead of an #Error! if either of those to fields contain a zero or null value?

 

Thanks

Best Answer chosen by Admin (Salesforce Developers) 
phiberoptikphiberoptik

Just to make it a better solution, here is the correct code:

 

IF(OR(Current_YTD_Sales_Annualized__c=0,Current_Year_Goal__c=0), 0,
IF(OR(ISBLANK(Current_YTD_Sales_Annualized__c),ISBLANK(Current_Year_Goal__c)), null,
Current_YTD_Sales_Annualized__c / Current_Year_Goal__c))

 

Feel free to change the solution to this one. Cheers!

All Answers

phiberoptikphiberoptik
IF(AND(NOT(ISBLANK(Current_YTD_Sales_Annualized__c)),NOT(ISBLANK(Current_Year_Goal__c))), (Current_YTD_Sales_Annualized__c / Current_Year_Goal__c), null)

 

JaxBeachJaxBeach

Thanks.  The syntax is coming up correct, but it still returns an #Error!

JaxBeachJaxBeach

does that formula just correct it if it is blank and not if one of the fields is zero?

phiberoptikphiberoptik

Yes, sorry, I neglected concern for if they are 0.

 

IF(OR(Current_YTD_Sales_Annualized__c=0,Current_Year_Goal__c=0), "0",
IF(OR(ISBLANK(Current_YTD_Sales_Annualized__c),ISBLANK(Current_Year_Goal__c)), null,
Current_YTD_Sales_Annualized__c / Current_Year_Goal__c))

 

 

 

JaxBeachJaxBeach

Got the following error message:

 

Error: Incorrect parameter type for function 'IF()'. Expected Text, received Number

phiberoptikphiberoptik

What is the output type of your formula field?

JaxBeachJaxBeach

% - Percentage

phiberoptikphiberoptik

Remove the quotation marks around the zero in the first line.

JaxBeachJaxBeach

Thank you so much!!!!!!!!!!!!  That worked :-)

phiberoptikphiberoptik

Just to make it a better solution, here is the correct code:

 

IF(OR(Current_YTD_Sales_Annualized__c=0,Current_Year_Goal__c=0), 0,
IF(OR(ISBLANK(Current_YTD_Sales_Annualized__c),ISBLANK(Current_Year_Goal__c)), null,
Current_YTD_Sales_Annualized__c / Current_Year_Goal__c))

 

Feel free to change the solution to this one. Cheers!

This was selected as the best answer
JaxBeachJaxBeach

Thanks again :-)