You need to sign in to do that

Don't have an account?

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%.

Swati PawarHi,

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 !!

## All Answers

Swati PawarHi,

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 !!

Felix QuinonesThanks for the formula.

I'm working with the whole formula. Now, I'm getting the error of the 5000 characters.

Felix QuinonesIt works. I remove characters from other formulas. Puja Agarwal 63It’s the most beautiful thing you will ever see. Lottery Sambad (https://lotterysambaddear.in)

Puja Agarwal 63Live for the moments you cannot put in words. Nagaland State Lottery (https://nagalandstatelottery.in)

Puja Agarwal 63Then, we stay ghost pals so we can be taking such wonderful pictures. Dhankesari (https://dhankesari.org)

Puja Agarwal 63I feel like I have known your whole life. Lottery Sambad (https://prokeyshop.com/lottery-sambad-result-today-12pm-4pm-8pm/)

Puja Agarwal 63So 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 hgfjngfI 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 9Hi, 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 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