 ShowAll Questionssorted byDate Posted Felix Quinones

# Custom division formula field do not produce expected values when fields left blank

Hello All,

I'm trying to create a formula field (Yearly Compliance) for a custom object named Compliance. The formula should add the compliance for the quarter (i.e. Q1_c__c), if not left blankand, and divide those by the number of complete fields. I'm trying to calculate the percent of compliance. The problem is that any of those four number fields could be left on black.

I tried the following formulas in where any of the Qs (i.e. Q1_c__c)  could be a number or could be left blank.

```IF(
AND
(Q1_c__c =0,
Q1_c__c = 0,
Q1_c__c = 0,
Q1_c__c = 0
),
0,
(Q1_c__c + Q2_c__c + Q3_c__c + Q4_c__c)/4)```

```IF(

AND(
ISBLANK(Q1_c__c),
ISBLANK(Q2_c__c),
ISBLANK(Q3_c__c),
ISBLANK(Q4_c__c)
),
NULL,(Q1_c__c + Q2_c__c + Q3_c__c + Q4_c__c)/4)```

Here is a picture of the pay layout .

• If the quarter is left black, means that do not apply.
• I'm trying to calculate the percent of Yearly Compliance. Here the Yearly Compliance should be 100% divided by 3. This is because the quarter empty (Q1) do not count for this year compliance. Also, the other two quarters are in 0%. Yearly compliance should be 33.33% Best Answer chosen by Felix Quinones Swati Pawar
Hi,

Below mentioned formula might help you.  Make sure to check "Treat blank fields as blanks" option in your formula field.

Formula :
IF(
(Q1__c = 0 ||ISBLANK(Q1__c)) && (Q2__c = 0 || ISBLANK(Q2__c)) && (Q3__c = 0 || ISBLANK(Q3__c)) && (Q4__c = 0 || ISBLANK(Q4__c)) ,
0 ,
((IF(ISBLANK(Q1__c),0,Q1__c) +
IF(ISBLANK(Q2__c),0,Q2__c) +
IF(ISBLANK(Q3__c),0,Q3__c) +
IF(ISBLANK(Q4__c),0,Q4__c) ) )/
(IF(ISBLANK(Q1__c),0,1)+ IF(ISBLANK(Q2__c),0,1) +
IF(ISBLANK(Q3__c),0,1) + IF(ISBLANK(Q4__c),0,1))

) If it works for you then mark it as a best answer.

Thanks !! Swati Pawar
Hi,

Below mentioned formula might help you.  Make sure to check "Treat blank fields as blanks" option in your formula field.

Formula :
IF(
(Q1__c = 0 ||ISBLANK(Q1__c)) && (Q2__c = 0 || ISBLANK(Q2__c)) && (Q3__c = 0 || ISBLANK(Q3__c)) && (Q4__c = 0 || ISBLANK(Q4__c)) ,
0 ,
((IF(ISBLANK(Q1__c),0,Q1__c) +
IF(ISBLANK(Q2__c),0,Q2__c) +
IF(ISBLANK(Q3__c),0,Q3__c) +
IF(ISBLANK(Q4__c),0,Q4__c) ) )/
(IF(ISBLANK(Q1__c),0,1)+ IF(ISBLANK(Q2__c),0,1) +
IF(ISBLANK(Q3__c),0,1) + IF(ISBLANK(Q4__c),0,1))

) If it works for you then mark it as a best answer.

Thanks !!
This was selected as the best answer Felix Quinones
Thanks for the formula.
I'm working with the whole formula. Now, I'm getting the error of the 5000 characters.  Felix Quinones
It works. I remove characters from other formulas. Puja Agarwal 63
It’s the most beautiful thing you will ever see. Puja Agarwal 63
Live for the moments you cannot put in words.
Nagaland State Lottery (https://nagalandstatelottery.in) Puja Agarwal 63
Then, we stay ghost pals so we can be taking such wonderful pictures.
Dhankesari (https://dhankesari.org) Puja Agarwal 63
I feel like I have known your whole life. Puja Agarwal 63 jnhfjhg hgfjngf simply buzzes 9 Nagaland Lottery Sambad