cancel
Showing results for
Search instead for
Did you mean:
Regular Visitor

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

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

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!

Regular Visitor

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

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!

## Helpful resources

Announcements

#### Power Apps News & Announcements

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

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

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (3,560)