Averaging fields, ignoring blanks

[Steve Vincent]

I have five fields on a report that contain scores from a survey. Sometimes
a question gets skipped, and that field is null. I would like to compute
the average, in a section footer, of only the fields in each record that
contain values. So, if only four of the five fields contain values, I want
to add those four fields together, and divide the total by FOUR, not by five.

I have seen a number of postings that are "almost" this situation, but not
quite. So, I have been researching like crazy, and trying many differnt IIF
statements, Nz, IsError, IsNull, expressions... and haven't found the
solution yet.

Let's call my five fields [T1], [T2], [T3], [T4], and [T5]. Any ideas for
computing the mean (avg) of only the data-containing fields? The formula
will live on the face of the report itself.

Thank you very much in advance,
Steve

 

[KARL DEWEY]

I would like to compute the average, in a section footer
How do you plan on putting multiple record averages in the footer?

This will take care of null fields ---
Average: (Nz([T1],0)+ Nz([T2],0)+ Nz([T3],0) +Nz([T4],0) Nz([T5],0))/ (5-
(Nz([T1],1)+ Nz([T2],1)+ Nz([T3],1) +Nz([T4],1) Nz([T5],1)))

Here the nulls are converted to 1's and subtracted from 5 to divide into the
sum of the fields.

 

[John W. Vinson]

I have five fields on a report that contain scores from a survey.

Then you have a spreadsheet, not a relational database.

If you ever need to add a question what will you do? Redesign your table, all
your forms, all your queries, all your reports!? Ouch. See:

Duane Hookum's "At Your Survey":
http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='At Your Survey 2000'

for a normalized alternative structure which avoids this problem.

Sometimes
a question gets skipped, and that field is null. I would like to compute
the average, in a section footer, of only the fields in each record that
contain values. So, if only four of the five fields contain values, I want
to add those four fields together, and divide the total by FOUR, not by five.

I have seen a number of postings that are "almost" this situation, but not
quite. So, I have been researching like crazy, and trying many differnt IIF
statements, Nz, IsError, IsNull, expressions... and haven't found the
solution yet.

Let's call my five fields [T1], [T2], [T3], [T4], and [T5]. Any ideas for
computing the mean (avg) of only the data-containing fields? The formula
will live on the face of the report itself.

=(NZ([T1]) + NZ([T2]) + NZ([T3]) + NZ([T4]) + NZ([T5])) / (IIF(IsNull([T1], 0,
1) + (IIF(IsNull([T2], 0, 1) + (IIF(IsNull([T3], 0, 1) + (IIF(IsNull([T4], 0,
1) + (IIF(IsNull([T5], 0, 1))

Even this will fail if *all five* answers are null.

Or, if you normalize, Avg([answer]). Just a bit simpler.

 

[John W. Vinson]

This will take care of null fields ---
Average: (Nz([T1],0)+ Nz([T2],0)+ Nz([T3],0) +Nz([T4],0) Nz([T5],0))/ (5-
(Nz([T1],1)+ Nz([T2],1)+ Nz([T3],1) +Nz([T4],1) Nz([T5],1)))

Here the nulls are converted to 1's and subtracted from 5 to divide into the
sum of the fields.

Karl, won't the denominator be the sum of the five fields if they're not null?
You'll need an expression like IIF(IsNull([T1]), 0, 1) to get numbers 1-5 in
the denominator, I'd think.