Need help with Average calculation....

[jacqueline]

I am trying to calculate the average of 2 English courses fields.

Seems simple except that not all applicants have values for these 2 grades
(Fields are 'Number' datatype).
Default grade for non-entries in either field is 0....so no Null values to
worry about.

Some applicants will have a grade for both fields: English_Grade and
English_Grade2.

Some will have a grade in the 1st field: English_Grade but a 0 in the 2nd
field English_Grade2 (just meaning they only did 1 course)

Some will have a 0 in the 1st field English_Grade...and a grade in the 2nd
field: English_Grade2 (also only means they did 1 course)

Others may have 2 0's...never did either course.

When calculating the average of the grades...I need to take all
possibilities into account...
So, for example...if English_Grade = 80 and English_Grade2 = 0....I want the
average for that applicant to equal 80....not divided by 2 to get 40....etc...

This report is just a list of all applicant names...and their english
prerequisite average.

How can I set up the query in an IIf statement (or multiple queries, etc) to
do all this...?
Do not want to use a VB function....

Thanks!

 

[Ken Snell [MVP]]

How do you differentiate between a zero (the person didn't take the course)
and a zero (that is the grade the person got).

The DAvg domain function will calculate averages for you, and Null values
are ignored -- as this seems to be what you want, I recommend that you
change 0 values to Null in the table (and change the default value for those
fields to Null as well).

Otherwise, you'll need to use an IIf function to exchange Null for the zero
in the DAvg function's argument for the "field to be averaged" (i.e., the
first argument of the function).

 

[jacqueline]

Thanks Ken....I think this works for what I need...
EngGrade:
((IIf((Nz([English_Grade],0)=0),[English_Grade2],[English_Grade]))+(IIf((Nz([English_Grade2],0)=0),[English_Grade],[English_Grade2])))/2

 

[John Spencer (MVP)]

You can use the switch function. Replace A and B with your field numbers.

Switch(A=0,B,B=0,A,True,A+B/2)

Thanks Ken....I think this works for what I need...
EngGrade:
((IIf((Nz([English_Grade],0)=0),[English_Grade2],[English_Grade]))+(IIf((Nz([English_Grade2],0)=0),[English_Grade],[English_Grade2])))/2

How do you differentiate between a zero (the person didn't take the course)
and a zero (that is the grade the person got).

The DAvg domain function will calculate averages for you, and Null values
are ignored -- as this seems to be what you want, I recommend that you
change 0 values to Null in the table (and change the default value for those
fields to Null as well).

Otherwise, you'll need to use an IIf function to exchange Null for the zero
in the DAvg function's argument for the "field to be averaged" (i.e., the
first argument of the function).