Running Total/Average Formula 2

[crsdev]

Hello Friends
When doing a running average or just average it there a way to exclude 0's and blanks that are in details..
ie
19
0
5

10
-------
avg. 6.8 ----it is (19+0+5+ +10)/5
----I want it to be (19+5+10)/3

Thanks.

 

[Madawc]

Do a running total count that excludes zeros and nulls using a formula. Divide this into the sum

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lbass]

Insert a running total that sums {table.amt}, evaluates using a formula:

not (
isnull({table.amt}) or
{table.amt} = 0
)

Reset never (Or on change of group if at the group level).

-LB

 

[sheu]

This thread hits the spot!

One issue I've run into using this approach is that if there are no components to average, the field is blank (ie, not even a 0).

How do I avoid this?

 

[chelseatech]

If a conditional running total doesn't select any data, the value returned is NULL. This might appear on the report as blank, but really it is NULL (No Data). Makes sense when you think about it in those terms.

Two solutions to fix this.
1) Under File/Report Options tick the box that says "Convert other NULL values to default. Warning: this might mess up some other NULL processing you might be using as required.
2) Include another formula in the report to conver the NULL for that running total to zero:
If IsNULL({#My Running Average}) then 0
else {#My Running Average}
Use this formula in your report instead of the running total field

Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[sheu]

I went with Option 2 and it appears to be working well.

Thanks both you and lbass

(Madawc, your post is good too but I needed to see it spelled out since I'm such a Crystal Noob)