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%