2 Replies Latest reply on Jul 12, 2012 6:37 AM by BruceHerbach

# Average function with condition

Hi,

I have a summary table where I use an average function in some of my calculation fields. This is basically my reference for my report. My question is, how do I get the average of fields that only have data or greater than 0 (see example below)?

Ex.

SV1-80

SV2-90

SV3-85

SV4-empty / null

The Average for this should be 85 and not 63 because I will not include SV4 in the computation.

• ###### 1. Re: Average function with condition

The aggregate function Average (field1 {; field2 ...} ) will return the average of all the non-blank fields specified.

This works both in a calculation field and a summary field.

In your case a calculation field with the content

Average ( SV1 ; SV2 ; SV3 ; SV4 )

where the fields SV1...SV4 are calculation fields, if one of the fields' calculation result is empty, that field will be excluded from the average.

Example:

Average ( 80 ; 90 ; 85 ; "" ) = 85

Average ( 80 ; 90 ; 85 ; 0 ) = 63.75

• ###### 2. Re: Average function with condition

In your Average Calc Field use a let statement to gather the data.  Something like this:

Let([

fld1 = if(table::fieldA = 0;"";table::fieldA;

fld2 = if(table::fieldB = 0;"";table::fieldB;

fld3 = if(table::fieldC = 0;"";table::fieldC;

fld4 = if(table::fieldD = 0;"";table::fieldD ];

Average(fld1;fld2;fld3;fld4))

-----

The values for the fldX variables will be empty if the actual field is 0. This will eliminate them from the Average function and should return the correct value.