How to calculate average with null values?

[duncanstanton]

I have a field that is adding up to four values together and then dividing by the number of values totaled

Example : 3,3,4,4 =14/4=3.5(avg) 

However when I have the following : 3,3,4,null=10/3=3.333(avg) I get null because when including the null in the total, you get null. I can replace the null value with a 0 by using the following : ($V{Total_Cost_1}==null?0:$V{Total_Cost_1}.doubleValue()), but this still leaves me with a problem as the number that I am dividing by is still 4 rather than 3. Does anyone have any advice on how to tackle this problem?

[C-Box]

Have you set the initial value of your variable $V{Total_Cost_1} to 0 (Zero)?

Otherwise perhaps a try:

What about using an own simple counter "$V{NotNullCounter}" with calc-type sum and initial value = 0 for later calculating the average by a simple  BigDecimal division :

something like

$V{Total_Cost_1} == null? $V{NotNullCounter}:1[/code]

and the average later in a simple field-expression:

$V{YourSimpleTotalCostSum}.divide($V{NotNullCounter}, 3, RoundingMode.Ceiling)[/code]

not tried yet... but should work (after some finetuning with trial&error probably) :-)

hth + regards

C-Box