Help with calculating average depending on varying denominator.

[ClipperSun]

I have the below objective assessment edit form, with 6 Objectives that need to be scored (0,3,5,7,10). ''Part B score'' field on top is the average of the invidual scores. I want the average calculated only for the objectives with content and score, as there may be varying number of objectives. In the below example,Part B score =  (3+5+5+3)/4. The denominator should change depending on number actual objectives 🤔. Please help me find a solution for this. Thank you.

[StalinPonnusamy · Accepted solution]

Hi @ClipperSun

This takes an average of 3 control. Same applies for any number of controls

Sum(Value(TextInput1.Text) + Value(TextInput1_1.Text) + Value(TextInput1_2.Text)) / Sum(
    If(
        !IsBlank(TextInput1.Text),
        1,
        0
    ),
    If(
        !IsBlank(TextInput1_1.Text),
        1,
        0
    ),    If(
        !IsBlank(TextInput1_2.Text),
        1,
        0
    )
)

[StalinPonnusamy · Accepted solution]

Hi @ClipperSun 

Works with 5 control. Use comma on the sum

Sum(
    Value(TextInput1.Text),
    Value(TextInput1_1.Text),
    Value(TextInput1_2.Text),
    Value(TextInput1_3.Text),
    Value(TextInput1_4.Text)
) / Sum(
    If(
        !IsBlank(TextInput1.Text),
        1,
        0
    ),
    If(
        !IsBlank(TextInput1_1.Text),
        1,
        0
    ),
    If(
        !IsBlank(TextInput1_2.Text),
        1,
        0
    ),
    If(
        !IsBlank(TextInput1_3.Text),
        1,
        0
    ),
    If(
        !IsBlank(TextInput1_4.Text),
        1,
        0
    )
)

Sample:

[ClipperSun · Accepted solution]

Thank you. Worked!

[RandyHayes · Accepted solution]

@StalinPonnusamy @ClipperSun 

You might find the formula simplified to be more manageable and easier to alter:

Average(
   Filter([TextInput1.Text, TextInput1_1.Text, TextInput1_2.Text, TextInput1_3.Text, TextInput1_4.Text], 
       !IsBlank(Value)
   ), Value(Value)
)