Exclude null values in average of a column of values?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a report in which I want to calculate the average length of time from
"PointA" to "PointB". Not all records HAVE a "PointB". Can I exclued those so
that I'm only averaging the folks who got to "PointB"?
 
Sum, Count, Avg etc do not include null points.
You can either put the calculation in the query
(use an Aggregate query to get the Aggregate functions),
or you can calculate the values on the report:
instead of choosing a field from a query, type in
a calculated value like this:
=avg(MyFieldWithNulls)

(david)
 
Not sure how to do it in a query (maybe I should head to the query board!)
but the calculated query in the report didn't work - when I tried to view the
report, it gave me a "parameter prompt" box. But I'm curious - what's the
logic of using the phrase "WithNulls" when I actually want to exclude null
values? Sometimes the logic of Access expressions eludes me...
 
If you are using DateDiff to calculate the length of time, then it will
return NULL if either date is null, so Just average the calculation.

Avg(DateDiff("s",[PointA],[PointB]))

You didn't say whether you were computing this in terms of seconds, minutes,
hours, days, weeks, months, years. I guessed at seconds.
 
WOAH!
My average is 15,724,800! Not sure, but that may be more days than healthy
people live! What am I doing wrong???

Many thanks!
--



John Spencer said:
If you are using DateDiff to calculate the length of time, then it will
return NULL if either date is null, so Just average the calculation.

Avg(DateDiff("s",[PointA],[PointB]))

You didn't say whether you were computing this in terms of seconds, minutes,
hours, days, weeks, months, years. I guessed at seconds.

NC_Sue said:
I have a report in which I want to calculate the average length of time
from
"PointA" to "PointB". Not all records HAVE a "PointB". Can I exclued those
so
that I'm only averaging the folks who got to "PointB"?
Click to expand...
Click to expand...
 
That is the number of SECONDS, As I said you didn't say what period you
wanted to calculate.

That number of seconds represents 182 days.

Change the "s" to "d" if you want days
Avg(DateDiff("d",[PointA],[PointB]))

NC_Sue said:
WOAH!
My average is 15,724,800! Not sure, but that may be more days than healthy
people live! What am I doing wrong???

Many thanks!
--



John Spencer said:
If you are using DateDiff to calculate the length of time, then it will
return NULL if either date is null, so Just average the calculation.

Avg(DateDiff("s",[PointA],[PointB]))

You didn't say whether you were computing this in terms of seconds,
minutes,
hours, days, weeks, months, years. I guessed at seconds.

NC_Sue said:
I have a report in which I want to calculate the average length of time
from
"PointA" to "PointB". Not all records HAVE a "PointB". Can I exclued
those
so
that I'm only averaging the folks who got to "PointB"?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top