cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ClipperSun
Advocate I
Advocate I

Help with calculating average depending on varying denominator.

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.

ClipperSun_1-1628166098764.png

 

4 ACCEPTED SOLUTIONS

Accepted Solutions
StalinPonnusamy
Community Champion
Community Champion

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

 

View solution in original post

StalinPonnusamy
Community Champion
Community Champion

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:

StalinPonnusamy_0-1628167702336.png

 

View solution in original post

Thank you. Worked!

View solution in original post

RandyHayes
Super User
Super User

@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)
)
_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

4 REPLIES 4
StalinPonnusamy
Community Champion
Community Champion

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

 

View solution in original post

StalinPonnusamy
Community Champion
Community Champion

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:

StalinPonnusamy_0-1628167702336.png

 

View solution in original post

Thank you. Worked!

View solution in original post

RandyHayes
Super User
Super User

@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)
)
_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (2,088)