Average formula with null

  • 0
  • 1
  • Question
  • Updated 10 months ago
  • In Progress
I have 6 Formula Numeric Fields that  Return a numeric number is a percent format... This is working just fine. But now I need to find the overall average for these 6 Fields that return a percent. But one or more of them may be blank.... 

Field Names Are: [Education Score] [Licensing Score] [Family Partnership Score] [Health Score] [Special Services Score] [Prenatal Score] 

All 6 of these fields are set as formula numeric and return the correct scores. Again there may be one or two of them that are blank if they have nothing to calculate. 

I'm not trying to create a Overall Average for those 6 fields. But I only want to average the fields that have a value. 

IE: 
Education = 100%
Licensing = 100%
Family Partnership = 88%
Health = 90%
Special Services = 60%
Prenatal = null 

OVERALL AVERAGE = 88%

I'm stuck at how to create a formula in the overall average that will ignore the null values, and only return an average for those fields that have numbers in them.... 
Photo of Neal

Neal

  • 100 Points 100 badge 2x thumb
  • Stumped.

Posted 10 months ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,306 Points 50k badge 2x thumb
Have you set the field properties for each of the score fields to not treat null as zero? The Average function will ignore nulls, which is what you want
Photo of Neal

Neal

  • 100 Points 100 badge 2x thumb

I have seen that option before, but it is not an option on any of the score items.

I little more background.. My Education Section has 14 different Text drop down questions, each question is answered "Compliant" or "Not Compliant"

I then have Formula Numeric Fields Called [Education-Compliant] and another [Education-Non Compliant].

Each of those has a formula to count up the "Complaints" and Non-Complaints:

Using this type of formula:

ToNumber([E1-1]="Compliant")+ToNumber([E1-2]="Compliant")+ToNumber([E1-3]="Compliant")+ToNumber([E1-4]="Compliant")+
ToNumber([E2-1]="Compliant")+ToNumber([E2-2]="Compliant")+ToNumber([E2-3]="Compliant")+ToNumber([E2-4]="Compliant")+
ToNumber([E3-1]="Compliant")+ToNumber([E3-2]="Compliant")+ToNumber([E3-3]="Compliant")+ToNumber([E3-4]="Compliant")+
ToNumber([E4-1]="Compliant")+ToNumber([E4-2]="Compliant")+ToNumber([E4-3]="Compliant")+ToNumber([E4-4]="Compliant")+
ToNumber([E5-1]="Compliant")+ToNumber([E5-2]="Compliant")+ToNumber([E5-3]="Compliant")+ToNumber([E5-4]="Compliant")+
ToNumber([E6-1]="Compliant")+ToNumber([E6-2]="Compliant")+ToNumber([E6-3]="Compliant")+ToNumber([E6-4]="Compliant")+
ToNumber([E7-1]="Compliant")+ToNumber([E7-2]="Compliant")+ToNumber([E7-3]="Compliant")+ToNumber([E7-4]="Compliant")

The Education Score Field Then has a formula of: [Education-Compliant]/(SUM( [Education-Compliant],[Education-Non Compliant])
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,306 Points 50k badge 2x thumb
Can you think of a change to your formula to calculate to null under the condition you don't want to include in the the average?  
can say in plain english under what conditions you want the above formula to calculate to null?

ie, you need to differentiate, I assume,  between a zero score and a null score.