cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ClipperSun
Helper I
Helper 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
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Top Kudoed Authors
Users online (946)