+ Start a Discussion
Felix QuinonesFelix 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%. 


Example
 

Yearly compliance should be 33.33%
Best Answer chosen by Felix Quinones
Swati PawarSwati 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))
 
)

User-added image


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

Thanks !!

All Answers

Swati PawarSwati 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))
 
)

User-added image


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

Thanks !!
This was selected as the best answer
Felix QuinonesFelix Quinones
Thanks for the formula. 
I'm working with the whole formula. Now, I'm getting the error of the 5000 characters. 
User-added image
Felix QuinonesFelix Quinones
It works. I remove characters from other formulas. 
Puja Agarwal 63Puja Agarwal 63
It’s the most beautiful thing you will ever see.
Lottery Sambad (https://lotterysambaddear.in)
Puja Agarwal 63Puja Agarwal 63
Live for the moments you cannot put in words.
Nagaland State Lottery (https://nagalandstatelottery.in)
Puja Agarwal 63Puja Agarwal 63
Then, we stay ghost pals so we can be taking such wonderful pictures.
Dhankesari (https://dhankesari.org)
Puja Agarwal 63Puja Agarwal 63
I feel like I have known your whole life.
Lottery Sambad (https://prokeyshop.com/lottery-sambad-result-today-12pm-4pm-8pm/)
Puja Agarwal 63Puja Agarwal 63
So you have been this cool since day one? I don’t know if you mind knowing this, but my buddy is the cutest guy around.
Lottery Sambad (https://keralastatelottery.in/lottery-sambad-result-today-1155-am-4pm-8pm/)
jnhfjhg hgfjngfjnhfjhg hgfjngf
I think you need to determine the full code extension from here (https://ittermann.de/) I hope it will help you to solve this equation issue.
hjsd sdshjsd sds
Hey, can you share the program file of your project? I want to test for pc (https://mostpc.com/) website.