function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ 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.
simply buzzes 9simply buzzes 9
Hi, Many thanks for your valuable information but i need your recomendations for my website KBC Head Office  (https://joinkbcgame.com) I am facing to add code for my site extention. Could you please have a look and fix it? i'll be very very thanksfull to you. 
Nagaland Lottery SambadNagaland Lottery Sambad
 I think i have put it up in the right form for my website Nagaland State Lottery Sambad Morning Result (https://dearlotterysambad.today) as we ll as Sikkim State lottery Sambad 1 PM 6PM and 8PM (https://dearlotterysambad.today/sikkim-state-lottery-sambad-live-result-today-morning-1-pm/) . I hope it is correct for the use in Dear lottery Sambad today (https://dearlotterysambad.today). Thanks
Monotosth SahMonotosth Sah
This division formula field has helped our developer code core gaming features such as Unlocked all cars and unlimited money for the Extreme Car driving simulator MOD. Visit- simulatorapkmod.com to download the apk file and formula.