How to exclude NULL's & zero while calculating average

[Former Member]

Hi Team,

I am calculating average for a field which display values like below ...

3.25

6.15

7.24

8.15

0.0

10.25

0.0

2.45

Some NULL's and zero's are coming in between the values.

How to exclude these NULL's & Zero's while calculating average.

Thanks,

Subash

[abhilash_kumar]

Hi Subash,

1) Create a formula with this code and place this on the Details:

shared numbervar avg;

shared numbervar cnt;

if not isnull({field_to_avg}) or {field_to_avg} <> 0 then

(

     avg := avg + {field_to_avg};

     cnt := cnt + 1;

);

'';

2) Create another formula to display the average and place it on the Report Footer:

shared numbervar avg;

shared numbervar cnt;

avg/cnt;

-Abhilash

[Former Member]

Hi Subash, the following points might help you in order to exclude data rows with null or zero average values from report

1. Section Expert > Details > ✓ Suppress (No Drill-Down)

2. Format Formula Editor > Use Formula > IIF(ISNULL({Command.Average}) or ({Command.Average}) <> 0, True, False)