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

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
Super User
Super User

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
Super User
Super User

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.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
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
Super User
Super User

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
Super User
Super User

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

 

Thank you. Worked!

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.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

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

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Difinity Conference 2022

Difinity Conference 2022

Register today for two amazing days of learning, featuring intensive learning sessions across multiple tracks, led by engaging and dynamic experts.

European SharePoint Conference

European SharePoint Conference

The European SharePoint Conference returns live and in-person November 28-December 1 with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Power Apps Ideas

Check out the New Ideas Site

We are excited to announce a new way to share your ideas for Power Apps!

Top Solution Authors
Top Kudoed Authors
Users online (5,065)