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

#### April 4th Copilot Studio Coffee Chat | Recording Now Available

Did you miss the Copilot Studio Coffee Chat on April 4th? This exciting and informative session with Dewain Robinson and Gary Pretty is now available to watch in our Community Galleries!   This AMA discussed how Copilot Studio is using the conversational AI-powered technology to aid and assist in the building of chatbots. Dewain is a Principal Program Manager with Copilot Studio. Gary is a Principal Program Manager with Copilot Studio and Conversational AI. Both of them had great insights to share with the community and answered some very interesting questions!     As part of our ongoing Coffee Chat AMA series, this engaging session gives the Community the unique opportunity to learn more about the latest Power Platform Copilot plans, where we’ll focus, and gain insight into upcoming features. We’re looking forward to hearing from the community at the next AMA, so hang on to your questions!   Watch the recording in the Gallery today: April 4th Copilot Studio Coffee Chat AMA