I am trying to get a percentage omitting a NULL field.
My formula consist of multiple sections each equaling a percentage; however, when a section Null it isn't calculating in the total scores.
Forum Discussion
Yes there is a long complicated formula. See below:
var number Qone=Case([1. Complies with uniform and appearance standards (Badge/ ID/ Uniform).], "Satisfactory", 1, "Improvement Needed", 0, "Not Applicable", 0, 0);
var number Qtwo=Case([2. Initializes phone to begin receiving delivery route within 15 minutes at home location and checks email.], "Satisfactory", 1, "Improvement Needed", 0, "Not Applicable", 0, 0);
var number Qthree=Case([3. Has all vehicle and customer supplies available. (see Supply list)], "Satisfactory", 1, "Improvement Needed", 0, "Not Applicable", 0, 0);
var number Qfour=Case([4. Completes and documents Pre-Trip Vehicle Inspection.], "Satisfactory", 1, "Improvement Needed", 0, "Not Applicable", 0, 0);
var number Qfive=Case([5. Has Vehicle Binder in vehicle.], "Satisfactory", 1, "Improvement Needed", 0, "Not Applicable", 0, 0);
var number Qsix=Case([6. Does the courier know how often their vehicle should be serviced?], "Satisfactory", 1, "Improvement Needed", 0, "Not Applicable", 0, 0);
var number Qseven=Case([7. Has mobile printer in vehicle and is properly powered (SDC ONLY)], "Satisfactory", 1, "Improvement Needed", 0, "Not Applicable", 0, 0);
If( ($Qone+$Qtwo+$Qthree+$Qfour+$Qfive+$Qsix+$Qseven) / (7 -
(If([1. Complies with uniform and appearance standards (Badge/ ID/ Uniform).]="Not Applicable",1, 0)+
If([2. Initializes phone to begin receiving delivery route within 15 minutes at home location and checks email.]="Not Applicable",1, 0)+
If([3. Has all vehicle and customer supplies available. (see Supply list)]="Not Applicable",1, 0)+
If([4. Completes and documents Pre-Trip Vehicle Inspection.]="Not Applicable",1, 0)+
If([5. Has Vehicle Binder in vehicle.]="Not Applicable",1, 0)+
If([6. Does the courier know how often their vehicle should be serviced?]="Not Applicable",1, 0)+
If([7. Has mobile printer in vehicle and is properly powered (SDC ONLY)]="Not Applicable",1, 0)))>0,
($Qone+$Qtwo+$Qthree+$Qfour+$Qfive+$Qsix+$Qseven)/(7-
(If([1. Complies with uniform and appearance standards (Badge/ ID/ Uniform).]="Not Applicable",1, 0)+
If([2. Initializes phone to begin receiving delivery route within 15 minutes at home location and checks email.]="Not Applicable",1, 0)+
If([3. Has all vehicle and customer supplies available. (see Supply list)]="Not Applicable",1, 0)+
If([4. Completes and documents Pre-Trip Vehicle Inspection.]="Not Applicable",1, 0)+
If([5. Has Vehicle Binder in vehicle.]="Not Applicable",1, 0)+
If([6. Does the courier know how often their vehicle should be serviced?]="Not Applicable",1, 0)+
If([7. Has mobile printer in vehicle and is properly powered (SDC ONLY)]="Not Applicable",1, 0))),
If( ($Qone+$Qtwo+$Qthree+$Qfour+$Qfive+$Qsix+$Qseven) / (7 -
(If([1. Complies with uniform and appearance standards (Badge/ ID/ Uniform).]="Not Applicable",1, 0)+
If([2. Initializes phone to begin receiving delivery route within 15 minutes at home location and checks email.]="Not Applicable",1, 0)+
If([3. Has all vehicle and customer supplies available. (see Supply list)]="Not Applicable",1, 0)+
If([4. Completes and documents Pre-Trip Vehicle Inspection.]="Not Applicable",1, 0)+
If([5. Has Vehicle Binder in vehicle.]="Not Applicable",1, 0)+
If([6. Does the courier know how often their vehicle should be serviced?]="Not Applicable",1, 0)+
If([7. Has mobile printer in vehicle and is properly powered (SDC ONLY)]="Not Applicable",1, 0)))=0, 0, 1))
var number Qone=Case([1. Complies with uniform and appearance standards (Badge/ ID/ Uniform).], "Satisfactory", 1, "Improvement Needed", 0, "Not Applicable", 0, 0);
var number Qtwo=Case([2. Initializes phone to begin receiving delivery route within 15 minutes at home location and checks email.], "Satisfactory", 1, "Improvement Needed", 0, "Not Applicable", 0, 0);
var number Qthree=Case([3. Has all vehicle and customer supplies available. (see Supply list)], "Satisfactory", 1, "Improvement Needed", 0, "Not Applicable", 0, 0);
var number Qfour=Case([4. Completes and documents Pre-Trip Vehicle Inspection.], "Satisfactory", 1, "Improvement Needed", 0, "Not Applicable", 0, 0);
var number Qfive=Case([5. Has Vehicle Binder in vehicle.], "Satisfactory", 1, "Improvement Needed", 0, "Not Applicable", 0, 0);
var number Qsix=Case([6. Does the courier know how often their vehicle should be serviced?], "Satisfactory", 1, "Improvement Needed", 0, "Not Applicable", 0, 0);
var number Qseven=Case([7. Has mobile printer in vehicle and is properly powered (SDC ONLY)], "Satisfactory", 1, "Improvement Needed", 0, "Not Applicable", 0, 0);
If( ($Qone+$Qtwo+$Qthree+$Qfour+$Qfive+$Qsix+$Qseven) / (7 -
(If([1. Complies with uniform and appearance standards (Badge/ ID/ Uniform).]="Not Applicable",1, 0)+
If([2. Initializes phone to begin receiving delivery route within 15 minutes at home location and checks email.]="Not Applicable",1, 0)+
If([3. Has all vehicle and customer supplies available. (see Supply list)]="Not Applicable",1, 0)+
If([4. Completes and documents Pre-Trip Vehicle Inspection.]="Not Applicable",1, 0)+
If([5. Has Vehicle Binder in vehicle.]="Not Applicable",1, 0)+
If([6. Does the courier know how often their vehicle should be serviced?]="Not Applicable",1, 0)+
If([7. Has mobile printer in vehicle and is properly powered (SDC ONLY)]="Not Applicable",1, 0)))>0,
($Qone+$Qtwo+$Qthree+$Qfour+$Qfive+$Qsix+$Qseven)/(7-
(If([1. Complies with uniform and appearance standards (Badge/ ID/ Uniform).]="Not Applicable",1, 0)+
If([2. Initializes phone to begin receiving delivery route within 15 minutes at home location and checks email.]="Not Applicable",1, 0)+
If([3. Has all vehicle and customer supplies available. (see Supply list)]="Not Applicable",1, 0)+
If([4. Completes and documents Pre-Trip Vehicle Inspection.]="Not Applicable",1, 0)+
If([5. Has Vehicle Binder in vehicle.]="Not Applicable",1, 0)+
If([6. Does the courier know how often their vehicle should be serviced?]="Not Applicable",1, 0)+
If([7. Has mobile printer in vehicle and is properly powered (SDC ONLY)]="Not Applicable",1, 0))),
If( ($Qone+$Qtwo+$Qthree+$Qfour+$Qfive+$Qsix+$Qseven) / (7 -
(If([1. Complies with uniform and appearance standards (Badge/ ID/ Uniform).]="Not Applicable",1, 0)+
If([2. Initializes phone to begin receiving delivery route within 15 minutes at home location and checks email.]="Not Applicable",1, 0)+
If([3. Has all vehicle and customer supplies available. (see Supply list)]="Not Applicable",1, 0)+
If([4. Completes and documents Pre-Trip Vehicle Inspection.]="Not Applicable",1, 0)+
If([5. Has Vehicle Binder in vehicle.]="Not Applicable",1, 0)+
If([6. Does the courier know how often their vehicle should be serviced?]="Not Applicable",1, 0)+
If([7. Has mobile printer in vehicle and is properly powered (SDC ONLY)]="Not Applicable",1, 0)))=0, 0, 1))