Average

[Kokobita]

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. 

[EricRegnier]

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!

[Kokobita]

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 · Accepted solution]

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!