Skip to main content
Question

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

  • June 22, 2017
  • 0 replies
  • 57 views

My formula consist of multiple sections each equaling a percentage; however, when a section Null it isn't calculating in the total scores. 
This topic has been closed for replies.

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.

It is a formula field. I don't want it to equal zero because it will hurt the score. 

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.

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)

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)

I had tried this originally and it doesn't work

What do you mean by "it does not work". Syntax error or incorrect totals?

I'm sorry I am getting the same result of blank

Can you post your formula?

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)

Is there data in any of those individual fields on the record you are testing on.  It should work.
Mark

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

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?

Yes, it is one of the section included in the total. It calculates the percentage of the questions answered. 

Satisfactory = 1
Improvement needed = 0
Not Applicable doesn't equal anything and shouldn't count in the percentage of that section. 

Are you saying that the field [Section Total:] in your test record has a value in it?.

Yes, it has a percentage. 

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.

I have already put in a support case. They said there will have to be a numeric value. 

I am not seeing your contact information. Can you please email me at cassie.reyes@fedex.com