A little Problem

[mama16]

What can I do to make Access to do the job. Every month I have to enter data into this DB and at the end of every month I have to run some reports. There are more columns similar to the one below with different values. For example, The one below, I have to count the values that are non zero, add them, and divide by how many there are(I can't divide by 24(Total) because there are zeros. So in this case is The sum of all the 14 values/ 14 = what I'm looking for!
Is there something I can do like
=SUM([Field_Name])/ "Something that will tell Access to ignore the zeros)?
I've tried using the count(*) and the ([Field_name])to do the job but it didn't work

Data
91.19%
94.90%
0.00%
0.00%
61.61%
100.00%
100.00%
90.00%
89.50%
0.00%
100.00%
88.44%
79.89%
0.00%
0.00%
0.00%
0.00%
95.01%
0.00%
0.00%
79.00%
74.00%
71.18%
0.00%


Appreciate

 

[PHV]

=SUM([Field_Name])/SUM(IIf([Field_Name]=0,0,1))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[mama16]

Does this means that you are = the fields to zero and if they are zero then convert them to one

=SUM([Field_Name])/SUM(IIf([Field_Name]=0,0,1))

 

[mama16]

Does this means that you are = the fields to zero if they are null and if they are zero then convert them to one

=SUM([Field_Name])/SUM(IIf([Field_Name]=0,0,1))

 

[PaulF]

no..

SUM([Field_Name])

means: sum all of the values in [Field_Name] regardless of the values


/SUM(IIf([Field_Name]=0,0,1))

means: But divide by the Sum of the following values

if [Field_Name] = 0 then the value = 0
however
if [Field_Name]<>0 then the value = 1


Which is what you want to do.. only divide by the number
of records that have a value <> 0.00%

PaulF