Average Help In Report

[rkrause]

I have a report, and in the report footer i have averages for different columns within the report. How do i get that average to exclude ZEROS.
Example below:

0
0
450
0
0
that average is 75 the way i have it now, and it should be excluding the ZEROS, and the avg should be 450

 

[tebule]

Add up all of the numbers and then do an average based on the row count values that do not equal 0. So.....
= a+b+c+d +e

next if the values are not in a table then you will have to evalueage each total for 0.
do until count > 5
if me.a = 0 then

loop

 

[tebule]

Sorry hit the wrong button and posted... you can do an if on each of the totals. Acutally it would be easier to do =

if me.a <> 0 then

 

[tebule]

UGRGGG.. anyway: if me.a <> 0 then countit = countit + 1

then you can do your average based on what countit equals. Hope this helps. It is just the way I would do it.

 

[rkrause]

So What i should do is a count for the detail row for example where they are not Equal to 0 Then add up my numbers and divide by that count number i get?

 

[rkrause]

OR what and where should my formula be?

 

[tebule]

Yup, that is what I would do. I would do it in the after update of the last total on the screen. But only you know if that makes since.

 

[rkrause]

whats the formula to do a count including not equal to 0

 

[tebule]

Please let this as it is a starting point for you:

Add up all of the numbers and then do an average based on the row count values that do not equal 0. So.....
= a+b+c+d +e
dim countit as Integer
'initialize countit
countit = 0
'next if the values are not in a table then you will have to evalueage each total for 0.
'------------------------------------------------------------------------------
'You could make this hard and loop through the collection, but to keep it simple and 'quick:

If me.a <> 0 then 'a= the field name
countit = countit + 1
else if me.b <> 0 then
countit = countit +1

'<Continue with the ifs and when you are done do the end ifs. Keep loop at the 'bottom. Complete this and let me know if you have questions.>
'At the end your fomula would be. Average = Total / countit.
loop

Hope this helps.

 

[rkrause]

Thanks for the help