Dealing with Null values when trying to Average across fields.

[EvanRosenlieb]

Hello all,



I have a large amount of field data that was taken by a data logger. Specifically, The datalogger has five temperature probes embedded in soil, and takes a measurement automatically once every hour for each probe. So the table has a Time field, and then a Temp1, Temp2,... Temp5 field. I need to be able to average the temperature fields. This within itself is easy enough, I just tell the query to find (Temp1+Temp2+...+Temp5)/5. However, unfortunately, because it is field data is is very common for one (or more) of the ports to be malfunctioning at any one measurement time for a number of reasons.

If, for instance, Port one does not have a value, then the formula I entered will not work, as it can't add a null value to numbers, and otherwise It would be dividing by five whereas there were only four values to average. Has anyone had to deal with a similar situation?

--Evan

[Robeen]

You could try using the Nz() function in a query.

The function gives you the option of telling Access what to use as a value should a Null be encountered.
Eg:
Select Nz([YourFieldName],0) From TableName;
This will give you the value if an actual value exists - or 0 if there is a null in one of the rows.

The other question - if there is a Null - and you get a 0 - do you want to include the 0 in the Averaging - as that will skew the average?

Or do you need to be able to check for all readings to be within a certain temp range . . .?
You can research the IIF functoin to use in your query.

[EvanRosenlieb]

Robeen - Thanks for the reply! I should have though of that, and after searching google about this topic in combination with NZ, I found the answer in another thread:

http://www.access-programmers.co.uk/.../t-136050.html

-Evan

[hertfordkc]

are two brute force solutions:
1) A calculated field in the query, using nested "iff" statements to determine the correct sum and divisor.
2) Process the recordset with Visual Basic.
Another possibility might exist if you know that the temperatures will always lie within a range. You might be able to infer what the divisor should be by looking at the sum of the readings, assuming that you use the NZ function on the fields so that the addition will always work, even if a sensor has failed. For example, if the temperature should always be between 100 and 119, then a sum of 500 or greater means the divisor should be 5, between 400 and 500, the divisor should be 4....