<div>
u can use IsNull(FIELD) to include it or not in the AVG(), something like:<br />
<br />
For each field in fields<br />
&nbsp; &nbsp;if not IsNull(field)<br />
&nbsp; &nbsp; &nbsp; SUM = SUM + field<br />
&nbsp; &nbsp; &nbsp; intNbrValues = intNbrValues + 1<br />
&nbsp; &nbsp;end if<br />
next<br />
<br />
&nbsp; AVG = SUM/intNbrValues 
</div>


u can use IsNull(FIELD) to include it or not in the AVG(), something like:

For each field in fields
   if not IsNull(field)
      SUM = SUM + field
      intNbrValues = intNbrValues + 1
   end if
next

  AVG = SUM/intNbrValues

<div>
Paasky, you have helped me agian. Thanks. The other answer was also helpful. Thank you!
</div>


Paasky, you have helped me agian. Thanks. The other answer was also helpful. Thank you!

<div>
Always happy to help you Susan. :o)<br />
<br />
Best regards,<br />
Paasky
</div>


Always happy to help you Susan. :o)

Best regards,
Paasky

<div>
<div class="content wysiwyg-content">
I have used Avg() to calculate the average of valus in a single field that may contain null values. Now I want to calculate the average of 5 values that are in different fields. It is possible that some of these values can be null and I will not want to include these in the average. In this case when I try to add the 5 values when one of them is null I come up with a null value. Can anyone help me wiht this? Thanks in advance.<br />
<br />
Susan
</div>
</div>


I have used Avg() to calculate the average of valus in a single field that may contain null values. Now I want to calculate the average of 5 values that are in different fields. It is possible that some of these values can be null and I will not want to include these in the average. In this case when I try to add the 5 values when one of them is null I come up with a null value. Can anyone help me wiht this? Thanks in advance.

Susan

AVG() or SUM() in different fields with nulls

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.