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?

 

[traingamer]

Try adding (modifying) a where clause in your record source like:

...where [Role] <> "fyi"

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[DanAtCDS]

Try this
=Sum(IIf([Role]="fyi",0,[your priority number])/Sum(IIf([Role]="fyi",0,1)

The first portion is getting the sum of all values where role <> "FYI"
The sencond portion is getting the count of records where role <> "FYI"

Hope this helps.
Dan

 

[jvhazelbaker]

I tried the second suggestion which produced an error. I'm not sure I understand how to the first suggestion. Any more ideas?

 

[traingamer]

Rather than having a table as the record source, I was implying that a query be the record source thus removing the FYI role completely from the display (and from the calculation).

How are you calculating totals and averages in a Form?

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[jvhazelbaker]

The form is actually completely set up, I'm just trying to add this one little feature. The reason it is based on a table is because the "fyi" field is a lookup. There are a number of different titles that may be selected. However, "fyi" is the only title I want excluded. So the way it is currently set up:

The main form is [Review and Edit Staffing on a Project]
The subform is [Priority Data Table subform1]
Both forms pull from queries.

The first lookup field in the subform is [Role], pulled from a lookup table.
The second field to be averaged is [Your Priority Number], a blank field to be filled in.

The subform's default view is database. In the form footer, which can't be seen is a field named Total with the following formula =avg([your priority number]).

On the main form there is a field with the formula
=[Priority Data Table subform1]!Total that gives the average. This is working fine, but is currently figuring the priority number for the fyi role.

Does this help?

 

[traingamer]

Replace [Role] in Dan's suggestion with the results of your Lookup.

OR
If the lookup is looking up a single roletype to returning 'FYI', use that field and roletype instead of [Role] and "fyi" in his code.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[jvhazelbaker]

I am pretty sure I don't understand.

This is the way the roles table is laid out

ID Role
-----------
1 Leader
2 Sponsor
3 Team Leader
4 FYI

I have tried replacing the "fyi" with the actual ID (4) but that isn't working either.

Is that what you are suggesting I do, or did I completely misinterpret what you were saying?

 

[traingamer]

Try replacing

=avg([your priority number])

in your footer with

=Sum(IIf([COLOR=red][ID][/color]=4,0,[your priority number])/Sum(IIf([COLOR=red][ID][/color]=4,0,1)

where [ID] is the field from your query that contains the roleID

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[jvhazelbaker]

That unfortunately didn't work. I'm assuming because the actual field name is Role. This is possible isn't it? I've been working on it for over a week now and nothing is working. There has to be some way.

 

[traingamer]

Then try it with [Role]=4...

 

[hkaing79]

When you say it doesn't work, what kind of errors are you getting? [your priority number] is number data type right?

Another method is to use DLookup on your query...
DLookup("Avg([your priority number]", "NameOfQuery", "Role not like 'fyi'")

 

[jvhazelbaker]

In the prior tries it was just saying Error in the box, with your formula I am getting #Name? in the box. I tried the formula you listed and got the return #Error. I also altered the formula. The "NameofQuery" was changed to the name of the main form to see if that would perhaps work, it returned #Error as well.

 

[hkaing79]

I hope you didn't use "NameOfQuery". You're supposed to use the name of the query you bound your form to.

I don't see anything wrong with Dan's suggestion

=Sum(IIf([Role]="fyi",0,[your priority number])/Sum(IIf([Role]="fyi",0,1)

Perhaps this...

=Sum(IIf([Priority Data Table subform1]![Role]="fyi",0,[Priority Data Table subform1]![your priority number])/Sum(IIf([Priority Data Table subform1]![Role]="fyi",0,1)

 

[jvhazelbaker]

There is no reason why these formulas aren't working, but for some they aren't. I even replaced "fyi" with the id for that role, "4", but it didn't work either.

 

[hkaing79]

http://tek-tips.com/viewthread.cfm?qid=1025824&page=1

The last comment by RoyVidar

Excerpt:
From what I see, one of the culprits for the #Name error, could be using the same name on controls as is the controlsource of a control.

 

[jvhazelbaker]

In response to the e-mail previous to the last, I didn't use "NameOfQuery" I actually used the name given to the form and/or query.

Second, I've never had that be an issue before, using the same name on a control as the controlsource. Upon making changes, it doesn't appear to change the outcome.

 

[hkaing79]

Maybe prepend Forms("Priority Data Table subform1").[Role] or Forms![Priority Data Table subform1]![Role] using Dan's suggestion.

If you don't need to see those values, I would filter out those records as traingamer originally suggested.

BTW, you said that the field you wanted is =[Priority Data Table subform1]!Total? Is Total a field in the subform? Perhaps you can adjust that field to exclude "fyi"? If you don't see a total field in datasheet view, try opening it up in form view and check out what's the control source for total.

 

[jvhazelbaker]

Total is a field not seen in the subform where the calculation is taking place. That is where I've tried excluding the "fyi".

As for including the specific syntax, I mapped the form and subform exactly, and it returned the error.

 

[hkaing79]

So that's where you set the control source for Total = Dan's formula?

If all else fails, can you filter out "fyi" in the subform's record source?