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

View solution in original post

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

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 II
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!

View solution in original post

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

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

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

secondImage

Demo Extravaganza is Back!

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

MBAS on Demand

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
Users online (85,033)