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%.
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
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 !!
I'm working with the whole formula. Now, I'm getting the error of the 5000 characters.
Lottery Sambad (https://lotterysambaddear.in)
Nagaland State Lottery (https://nagalandstatelottery.in)
Dhankesari (https://dhankesari.org)
Lottery Sambad (https://prokeyshop.com/lottery-sambad-result-today-12pm-4pm-8pm/)
Lottery Sambad (https://keralastatelottery.in/lottery-sambad-result-today-1155-am-4pm-8pm/)