Average (fields with 0 values are also counted)

[jodie]

Hi

I am trying to calculate the averages of fields called "Estimated Transfer" and "Estimated Deposit"

My problem is that I only want to average the fields where the value > 0. Even if I suppress the field, the field is still counted.

For eg.
Estimated Deposit Estimated Transfer
200 40
40 0
0 50
50 20

Average for estimated deposit should be 96.6 but it comes out to 72.5 (because it divides the sum of the estimated deposit by 4 instead of 3).

I feel the Average (fld, condFld) or Average (fld,condfld,cond) maybe able to help me. But I don't know how to write that the condition should be "average for fields where estimated deposit > 0 . Whichever way I write the formula it gives me an error

Please help.

thanks
Jodie

 

[idle]

The CondField is the field used for grouping the aggregate function, not for including the field in average.
The average is too low because too many "deposits" are counted. Therefore, make your own "counter" that gives you the correct number.

//Create a formula for counting deposits.
Global NumberVar Counter ;
If {Deposit} > 0 then
Counter := Counter + 1
Else
Counter ;

Calculate your average by:
Global NumberVar Counter ;
Sum({Deposit})/Counter