Calculating Averages

  • Thread starter Thread starter Alex H
  • Start date Start date
A

Alex H

Hi, I have a sub form which lists the records related to the main form.

Two of the fields of the sub form are [Phase] and [Marks]

In the footer of the sub form, I want to calculate the average [Marks],
where the [Phase] field = 1.
The code I have used is: =Avg(IIf([Phase]=1,[Marks],0)).

What is happening is that it is calculating the Average of all the records
displayed in the sub form, including those where [Phase] is not equal to 1.

Can anyone help please?

Tks
A
 
You might try:

=Iif([Phase] = 1,Avg([Marks]),0)

This assumes that Phase is numeric. If text, use "1".

HTH,
Debbie

| Hi, I have a sub form which lists the records related to the main form.
|
| Two of the fields of the sub form are [Phase] and [Marks]
|
| In the footer of the sub form, I want to calculate the average [Marks],
| where the [Phase] field = 1.
| The code I have used is: =Avg(IIf([Phase]=1,[Marks],0)).
|
| What is happening is that it is calculating the Average of all the records
| displayed in the sub form, including those where [Phase] is not equal to 1.
|
| Can anyone help please?
|
| Tks
| A
|
|
|
|
 
Hi Alex

All numeric values, including zero, will be included in the Avg calculation.
However, a value of Null will be ignored and will not be included in the
count. So change your expression to:
=Avg(IIf([Phase]=1,[Marks],Null))
 
That got it Graham - thanks

A

Graham Mandeno said:
Hi Alex

All numeric values, including zero, will be included in the Avg
calculation. However, a value of Null will be ignored and will not be
included in the count. So change your expression to:
=Avg(IIf([Phase]=1,[Marks],Null))

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Alex H said:
Hi, I have a sub form which lists the records related to the main form.

Two of the fields of the sub form are [Phase] and [Marks]

In the footer of the sub form, I want to calculate the average [Marks],
where the [Phase] field = 1.
The code I have used is: =Avg(IIf([Phase]=1,[Marks],0)).

What is happening is that it is calculating the Average of all the
records displayed in the sub form, including those where [Phase] is not
equal to 1.

Can anyone help please?

Tks
A
Click to expand...
Click to expand...
 
Back
Top