I am trying to get a percentage omitting a NULL field.

• 0
• Question
• Updated 2 years ago
My formula consist of multiple sections each equaling a percentage; however, when a section Null it isn't calculating in the total scores.
• 470 Points

Posted 2 years ago

• 0
• 72,680 Points
Are you looking to treat a null as a zero?  Can you tell e the type of field you are totaling.  is this a data entry field or a formula field or a summary field.
• 470 Points
It is a formula field. I don't want it to equal zero because it will hurt the score.
• 72,680 Points
Is this a Total or an Average?  if it is a total, then the zero will not hurt the total as it will be zero.
• 470 Points
Here is the formula: ([Section Total:]* 0.05)+([Section Total]*0.25)+([Unnamed 5]*0.1)+([Unnamed 6]*0.03)+([Unnamed 7]*0.12)+([Unnamed 8]*0.15)+([Unnamed 10]*0.25)+([Section Total;]*0.05)
• 72,680 Points
Try this

nz([Section Total:]* 0.05)
+nz([Section Total]*0.25)
+nz([Unnamed 5]*0.1)
+nz([Unnamed 6]*0.03)
+nz([Unnamed 7]*0.12)
+nz([Unnamed 8]*0.15)
+nz([Unnamed 10]*0.25)
+nz([Section Total;]*0.05)
• 470 Points
I had tried this originally and it doesn't work
• 72,680 Points
What do you mean by "it does not work". Syntax error or incorrect totals?
• 470 Points
I'm sorry I am getting the same result of blank
• 72,680 Points
• 470 Points
Nz([Section Total:]* 0.05)
+Nz([Section Total]*0.25)
+Nz([Unnamed 5]*0.1)
+Nz([Unnamed 6]*0.03)
+Nz([Unnamed 7]*0.12)
+Nz([Unnamed 8]*0.15)
+Nz([Unnamed 10]*0.25)
+Nz([Section Total;]*0.05)
• 72,680 Points
Is there data in any of those individual fields on the record you are testing on.  It should work.
Mark
• 470 Points
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))
• 72,680 Points
I assume that is a formula for one of the section fields.  But does that formula calculate to anything for the record that you are testing with?
• 470 Points
Yes, it is one of the section included in the total. It calculates the percentage of the questions answered.
• 470 Points
Satisfactory = 1
Improvement needed = 0
Not Applicable doesn't equal anything and shouldn't count in the percentage of that section.
• 72,680 Points
Are you saying that the field [Section Total:] in your test record has a value in it?.
• 470 Points
Yes, it has a percentage.
• 72,680 Points
I think that you will have to contact support to get them to help you, or else contact me off line via the information in my profile  or website at QuickBaseCoach.com and I will have a quick look myself.
• 470 Points
I have already put in a support case. They said there will have to be a numeric value.
• 470 Points
I am not seeing your contact information. Can you please email me at cassie.reyes@fedex.com