Exclude null values in average of a column of values?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

NC_Sue - 06 Jan 2006 02:10 GMT

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"?

Signature

Thanks for your time!

Reply to this Message

david@epsomdotcomdotau - 06 Jan 2006 10:00 GMT

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)

> 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"?

Reply to this Message

NC_Sue - 06 Jan 2006 13:12 GMT

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...

Signature

Again, thanks for your time!

> Sum, Count, Avg etc do not include null points.
> You can either put the calculation in the query
[quoted text clipped - 11 lines]
> so
> > that I'm only averaging the folks who got to "PointB"?

Reply to this Message

John Spencer - 06 Jan 2006 13:15 GMT

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.

>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"?

Reply to this Message

NC_Sue - 06 Jan 2006 13:44 GMT

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!

Signature

> 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.
[quoted text clipped - 9 lines]
> > so
> > that I'm only averaging the folks who got to "PointB"?

Reply to this Message

John Spencer - 06 Jan 2006 15:00 GMT

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]))

> WOAH!
> My average is 15,724,800! Not sure, but that may be more days than healthy
[quoted text clipped - 17 lines]
>> > so
>> > that I'm only averaging the folks who got to "PointB"?

Reply to this Message