cancel
Showing results for
Did you mean:

## 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.

4 ACCEPTED SOLUTIONS

Accepted Solutions
Super User

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

Super User

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:

Thank you. Worked!

Super User

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!
4 REPLIES 4
Super User

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

Super User

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:

Thank you. Worked!

Super User

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!

Announcements

#### December 2023 User Group Update: Welcoming New Groups and Upcoming Events

A new month means it's time to celebrate and welcome the new user groups that have joined our community. We are excited to announce that we have more than 8 New Groups, which is no surprise after the amazing Microsoft Power Platform Conference. This month, we are breaking them out by the different community categories. If your group is listed here, give this post a kudo so we can celebrate with you! Don't forget to take a look at the many events happening near you or virtually! It's a great time of year to connect and engage with User Groups both locally and online.   Please Welcome Our NEW User Groups   Power Platform: PowerIT User Group: Nottingham Power Platform User Group: Bergen Power Platform User Group: Danmark Nashville Power Platform User Group Microsoft Ambassador Program y Mujer Latina Technolochicas NCWIT Community Copilot Studio:  Copilot User Group Italia Dynamics365: Dynamics User Group AdriaticDynamic 365 Azerbaijan   December User Group Events   01 Dec 2023 Aprendiendo Desarrollo web, creando mi primer power app y power page. 01 Dec 2023 Q4 Hybrid Philadelphia Dynamics 365 & Power Platform User Group Meeting05 Dec 2023APAC Dynamics 365 FastTrack Bootcamp - BI and Analytics07 Dec 2023Bay Area Power Platform Meetup: Learn, Share, and Connect07 Dec 2023Indiana D365/AX December User Group Meeting07 Dec 2023Dynamics User Group Meeting: Houston09 Dec 2023 December '23 - NEW Power Apps User Group Meeting - Online 12 Dec 2023December Cleveland Power Platform User Group Meeting12 Dec 2023 RW2 Data Stewardship Open Forum Discussion 13 Dec 2023  Black Country Power Platform User Group - December 2023 - West Midlands