Forum Discussion

NealMathews's avatar
NealMathews
Qrew Trainee
8 years ago

Average formula with null

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.... 
Formulas and functions
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    8 years ago
    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
  • NealMathews's avatar
    NealMathews
    Qrew Trainee
    8 years ago

    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])
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    8 years ago
    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.