cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Kokobita
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
EricRegnier
Super User
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!

View solution in original post

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

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. 

EricRegnier
Super User
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 & Annoucements carousel

Power Apps News & Announcements

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

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Users online (4,693)