Removing Zero's From Averages

  • Thread starter Thread starter Arturo
  • Start date Start date
A

Arturo

I have 25 fields on a form that represents different categories for an
individual. Each day a score is supposed to be given for each category. (The
default is set at zero.) However, it is not always possible to rate every
category every day.

At the end of the week a report is generated that shows:
--The scores for each category for each day of the week
--The sum of the scores for the week
--The average score for each category for the week

The formula I am using for the average is:

=([M]+[T]+[W]+[R]+[F])/[DayCt] (DayCt is usually 5 unless a holiday)

I have been asked to remove the days that are zero from the formula that
calculates the average.

Currently, I would get this average for this example: (1+2+0+4+5)/5 = 2.4

The answer I need is 3.

What expression should I use to get the average of 3?

Thank you.
 
I'd actually recommend using the Nz function, in case Null values arise.

=(Nz([M],0)+Nz([T],0)+Nz([W],0)+Nz([R],0)+Nz([F], 0))/(IIf(Nz([M],0) = 0, 0,
1) + IIf(Nz([T], 0) = 0, 0, 1) + IIf(Nz([W], 0) = 0, 0, 1) + IIf(Nz([R], 0)
= 0, 0, 1) + IIf(Nz([F], 0) = 0, 0, 1))

Of course, now you run the risk of division by 0 if all of five fields are
zero.
 
This expression should work.

= ([M]+[T]+[W]+[R]+[F])/ ABS(([M]<>0)+([T]<>0)+([W]<>0)+([R]<>0)+([F]<>0))



John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
John, yours was easier for me to understand. I used yours and it works great!
Thanks.

John Spencer said:
This expression should work.

= ([M]+[T]+[W]+[R]+[F])/ ABS(([M]<>0)+([T]<>0)+([W]<>0)+([R]<>0)+([F]<>0))



John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
I have 25 fields on a form that represents different categories for an
individual. Each day a score is supposed to be given for each category. (The
default is set at zero.) However, it is not always possible to rate every
category every day.

At the end of the week a report is generated that shows:
--The scores for each category for each day of the week
--The sum of the scores for the week
--The average score for each category for the week

The formula I am using for the average is:

=([M]+[T]+[W]+[R]+[F])/[DayCt] (DayCt is usually 5 unless a holiday)

I have been asked to remove the days that are zero from the formula that
calculates the average.

Currently, I would get this average for this example: (1+2+0+4+5)/5 = 2.4

The answer I need is 3.

What expression should I use to get the average of 3?

Thank you.
Click to expand...
Click to expand...
 
As Douglas noted my expression will work as long as
== none of the fields ([M],[T],[W],[R], or [F] are null (blank)
== at least ONE field contains a value other than zero

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
John, yours was easier for me to understand. I used yours and it works great!
Thanks.

John Spencer said:
This expression should work.

= ([M]+[T]+[W]+[R]+[F])/ ABS(([M]<>0)+([T]<>0)+([W]<>0)+([R]<>0)+([F]<>0))



John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
I have 25 fields on a form that represents different categories for an
individual. Each day a score is supposed to be given for each category. (The
default is set at zero.) However, it is not always possible to rate every
category every day.

At the end of the week a report is generated that shows:
--The scores for each category for each day of the week
--The sum of the scores for the week
--The average score for each category for the week

The formula I am using for the average is:

=([M]+[T]+[W]+[R]+[F])/[DayCt] (DayCt is usually 5 unless a holiday)

I have been asked to remove the days that are zero from the formula that
calculates the average.

Currently, I would get this average for this example: (1+2+0+4+5)/5 = 2.4

The answer I need is 3.

What expression should I use to get the average of 3?

Thank you.
Click to expand...
Click to expand...
Click to expand...
 
Be aware that with John's, too, you'lll have a problem should all five
fields be zero.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Arturo said:
John, yours was easier for me to understand. I used yours and it works
great!
Thanks.

John Spencer said:
This expression should work.

= ([M]+[T]+[W]+[R]+[F])/
ABS(([M]<>0)+([T]<>0)+([W]<>0)+([R]<>0)+([F]<>0))



John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
I have 25 fields on a form that represents different categories for an
individual. Each day a score is supposed to be given for each category.
(The
default is set at zero.) However, it is not always possible to rate
every
category every day.

At the end of the week a report is generated that shows:
--The scores for each category for each day of the week
--The sum of the scores for the week
--The average score for each category for the week

The formula I am using for the average is:

=([M]+[T]+[W]+[R]+[F])/[DayCt] (DayCt is usually 5 unless a holiday)

I have been asked to remove the days that are zero from the formula
that
calculates the average.

Currently, I would get this average for this example: (1+2+0+4+5)/5 =
2.4

The answer I need is 3.

What expression should I use to get the average of 3?

Thank you.
Click to expand...
Click to expand...
Click to expand...
 
Thanks to both of you. If they are all zero, it is a moot point anyway. Now,
how do I get an average for the averages?

Thanks again.

John Spencer said:
As Douglas noted my expression will work as long as
== none of the fields ([M],[T],[W],[R], or [F] are null (blank)
== at least ONE field contains a value other than zero

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
John, yours was easier for me to understand. I used yours and it works great!
Thanks.

John Spencer said:
This expression should work.

= ([M]+[T]+[W]+[R]+[F])/ ABS(([M]<>0)+([T]<>0)+([W]<>0)+([R]<>0)+([F]<>0))



John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Arturo wrote:
I have 25 fields on a form that represents different categories for an
individual. Each day a score is supposed to be given for each category. (The
default is set at zero.) However, it is not always possible to rate every
category every day.

At the end of the week a report is generated that shows:
--The scores for each category for each day of the week
--The sum of the scores for the week
--The average score for each category for the week

The formula I am using for the average is:

=([M]+[T]+[W]+[R]+[F])/[DayCt] (DayCt is usually 5 unless a holiday)

I have been asked to remove the days that are zero from the formula that
calculates the average.

Currently, I would get this average for this example: (1+2+0+4+5)/5 = 2.4

The answer I need is 3.

What expression should I use to get the average of 3?

Thank you.
Click to expand...
Click to expand...
Click to expand...
 
Never mind. I copied the previous formula and had to add a field. I missed
one side of the parentheses. I got it.

Thanks again.

Arturo said:
Thanks to both of you. If they are all zero, it is a moot point anyway. Now,
how do I get an average for the averages?

Thanks again.

John Spencer said:
As Douglas noted my expression will work as long as
== none of the fields ([M],[T],[W],[R], or [F] are null (blank)
== at least ONE field contains a value other than zero

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
John, yours was easier for me to understand. I used yours and it works great!
Thanks.

:

This expression should work.

= ([M]+[T]+[W]+[R]+[F])/ ABS(([M]<>0)+([T]<>0)+([W]<>0)+([R]<>0)+([F]<>0))



John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Arturo wrote:
I have 25 fields on a form that represents different categories for an
individual. Each day a score is supposed to be given for each category. (The
default is set at zero.) However, it is not always possible to rate every
category every day.

At the end of the week a report is generated that shows:
--The scores for each category for each day of the week
--The sum of the scores for the week
--The average score for each category for the week

The formula I am using for the average is:

=([M]+[T]+[W]+[R]+[F])/[DayCt] (DayCt is usually 5 unless a holiday)

I have been asked to remove the days that are zero from the formula that
calculates the average.

Currently, I would get this average for this example: (1+2+0+4+5)/5 = 2.4

The answer I need is 3.

What expression should I use to get the average of 3?

Thank you.
Click to expand...
Click to expand...
Click to expand...
 
Hi Arturo,

I see you have a working solution already, but I thought I'd chime in with a
suggestion to use the method shown in this Knowledge Base (KB) article:

ACC2000: How to Calculate Row-Level Statistics
http://support.microsoft.com/kb/209839

Disregard the "ACC2000:" in the title, as the methods shown should work with
all versions of Access. There is some VBA code involved, but you can copy the
code from the KB article, and paste it into a new module as indicated (ie. no
need to retype it all).

The method is tolerant of nulls, however, it does treat zeros as a valid
number rather than an indicator to not include 0 in the statistical
calculations. If you wanted to go with this method, you could run update
queries to update all 0's to Null (as long as zero is not a valid result for
your data).

The table design that you have, with separate fields for [M], [T], [W], [R],
and [F] is not considered a properly normalized design. What you have is
known as a multivalued field design. This type of design will always be
harder to work with versus a normalized structure. You might want to download
the first two papers, written by Michael Hernandez, author of the book
"Database Design for Mere Mortals" and study them:

http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101

There are other DB design papers as well, but I'd make sure to read the two
papers written by Michael Hernandez.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________
 
Back
Top