cancel
Showing results for
Did you mean:
New Member

## Average

I have 5 dropdown menus with values from 0 to 3. Originally the idea was to get the average of the dropdown values. That was easy. However now, user wants a N/A option and not calculate the 0s. Is there a way to create a dynamic formula to calculate the average where the N/A doesn't affect the result.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User II

That's ok 🙂 With your formula I can see you're using canvas apps. To exclude N/As you need to modify your formula with something like this.

Don't forget to update the value of "N/A" to the right one...

``````RoundDown(
If(Dropdown1.Selected.Value <> "N/A", Value(Dropdown1.Selected.Value), 0)
+ If(Dropdown2.Selected.Value <> "N/A", Value(Dropdown2.Selected.Value), 0)
+ If(Dropdown3.Selected.Value <> "N/A", Value(Dropdown3.Selected.Value), 0)
/
If(Dropdown1.Selected.Value <> "N/A", 1, 0)
+ If(Dropdown2.Selected.Value <> "N/A", 1, 0)
+ If(Dropdown3.Selected.Value <> "N/A", 1, 0)
, 1)``````

Hope this helps!

3 REPLIES 3
Super User II

Hi @Kokobita, a few questions 🙂

How are you calculating the averages? Within a canvas app, or Dataverse calculated fields/columns? Also is your dropdown an option set (aka choice) column? Cheers!

New Member

I am sorry @EricRegnier , I don't know much of the concepts. I just can say that I created a couple of screens, inserted 3 dropdowns , assigned the values as [1,2,3] and calculated the average in a label with the formula : RoundDown(Value(Dropdown1.Selected.Value + Dropdown2.Selected.Value + Dropdown3.Selected.Value)/3,1)

It worked for me for a while, but now the user wants to add a new value N/A and now the average needs to be calculated only with the number of dropdowns with numbers and evade the dropdowns selected with N/A.

Super User II

That's ok 🙂 With your formula I can see you're using canvas apps. To exclude N/As you need to modify your formula with something like this.

Don't forget to update the value of "N/A" to the right one...

``````RoundDown(
If(Dropdown1.Selected.Value <> "N/A", Value(Dropdown1.Selected.Value), 0)
+ If(Dropdown2.Selected.Value <> "N/A", Value(Dropdown2.Selected.Value), 0)
+ If(Dropdown3.Selected.Value <> "N/A", Value(Dropdown3.Selected.Value), 0)
/
If(Dropdown1.Selected.Value <> "N/A", 1, 0)
+ If(Dropdown2.Selected.Value <> "N/A", 1, 0)
+ If(Dropdown3.Selected.Value <> "N/A", 1, 0)
, 1)``````

Hope this helps!

Announcements

#### Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

#### Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Test your skills now with the Cloud Skill Challenge.

#### Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

#### Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Top Solution Authors
Top Kudoed Authors
Users online (85,033)