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

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
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Community Call Conversations

Introducing the Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

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