Report Calculations

[Nick]

I am trying to gather statistics for my job. I know I can creat a query that
I can filter information out of, but I would like to have the report do that
if possible, that way I can cut down on the total number of queries I have.
Basically I want to calculate the average test scores for students admitted
to our program. Basically I have two different fuctions going on:

1) I need to pull only the admitted students
2) I need to calculate the average test scores

Here is the formula I came up with, but it calculates all of the scores, not
the specific ones I want:

=Avg([GRE V Score] And [Application Status]="Admit")

Can anyone help me with this?

 

[Evi]

Filter the query on which the report is based using "Admit" in the criterial
row under Application Status so that it only shows the pupils admitted.

See if this works better - if you have 1 record per student
in your report footer have

=(Sum([GRE V Score] )/Count(*)) * 100
Evi

 

[Evi]

Oh heck, brain fade

the sum should be (of course)

=Sum([GRE V Score] )/Count

Groan!

Evi

Filter the query on which the report is based using "Admit" in the criterial
row under Application Status so that it only shows the pupils admitted.

See if this works better - if you have 1 record per student
in your report footer have

=(Sum([GRE V Score] )/Count(*)) * 100
Evi

I am trying to gather statistics for my job. I know I can creat a query that
I can filter information out of, but I would like to have the report do that
if possible, that way I can cut down on the total number of queries I have.
Basically I want to calculate the average test scores for students admitted
to our program. Basically I have two different fuctions going on:

1) I need to pull only the admitted students
2) I need to calculate the average test scores

Here is the formula I came up with, but it calculates all of the scores, not
the specific ones I want:

=Avg([GRE V Score] And [Application Status]="Admit")

Can anyone help me with this?

 

[John Spencer]

One method

= Avg(IIF([Application Status]="Admit",[GRE V Score] ,Null))

Another method
=Abs(Avg([GRE V Score] * ([Application Status]="Admit")))

This works because [Application Status]="Admit" will return 0 if Status is not
Admit and -1 if Status is Admit). The Abs will take care of the number being
negative.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[Nick]

John,

Thank you so much. The first method did exactly what I wanted it to!

One method

= Avg(IIF([Application Status]="Admit",[GRE V Score] ,Null))

Another method
=Abs(Avg([GRE V Score] * ([Application Status]="Admit")))

This works because [Application Status]="Admit" will return 0 if Status is not
Admit and -1 if Status is Admit). The Abs will take care of the number being
negative.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

I am trying to gather statistics for my job. I know I can creat a query that
I can filter information out of, but I would like to have the report do that
if possible, that way I can cut down on the total number of queries I have.
Basically I want to calculate the average test scores for students admitted
to our program. Basically I have two different fuctions going on:

1) I need to pull only the admitted students
2) I need to calculate the average test scores

Here is the formula I came up with, but it calculates all of the scores, not
the specific ones I want:

=Avg([GRE V Score] And [Application Status]="Admit")

Can anyone help me with this?