Formula to exclude selection from average

[jvhazelbaker]

I'm trying to average a group of numbers in a field named [your priority number]. The form is in database view. The form includes the following fields:

Your Name
Role
Your Priority Number

Which in form view could look like this:

Your Name Role Your Priority Number
Jaime Team Leader 21
Angela FYI 99
Theresa Sponsor 13

When "FYI" is the selected role, I want that number to be excluded from the average. So rather than the average being 44.33, the formula would produce 17. I have tried the following with no success:

=iif([Role]="fyi",[your priority number]=0,sum([your priority number]))
and
=iif([Role]="fyi",[your priority number] is null,sum([your priority number]))

Any suggestions?

 

[jvhazelbaker]

How would I go about filtering out "fyi"?

 

[hkaing79]

Change the control source of your subform to exclude fyi in your WHERE clause or change your Filter Property on the subform.