5 Replies Latest reply on Jun 9, 2016 3:50 AM by MikeWile

# Don't consider zero in average calculation

I have a field, R_avg, that holds a value. There are five fields--R1, R2, R3, R4, and R5--that hold values from 0 to 5. If a field contains zero, I don't want it figured in the calc. So if the values are 5, 5, 3, 1, 1, R_avg would contain 15/5 or 3. If the values were 5, 5, 1, 0, 0, R_avg should contain 11/3. Is there a way to set up R_avg as a calc field that would add the five fields and divide by the count of non-zero values?

Right now I have it in a script trigger when one of the fields is changed. It starts with a COUNT=5 and looks at each of the R values, decrementing COUNT every time one of the values is zero. It then adds them together and divides by COUNT, but if COUNT is 0, it just adds 0 to R_avg. I'd rather put the calc in R_avg if I can.

• ###### 1. Re: Don't consider zero in average calculation

A bit verbose …

Let ( [

theCount =  GetAsBoolean ( R1 ) + GetAsBoolean ( R2 ) + GetAsBoolean ( R3 ) + GetAsBoolean ( R4 ) + GetAsBoolean ( R5 ) ;

theSum = R1 + R2 + R3 + R4 + R5

] ;

Case ( theCount ; theSum / theCount )

)

• ###### 2. Re: Don't consider zero in average calculation

Putting a null "" value instead of a 0 would make it work but, depending on what else you use the fields for it could cause other problems.

• ###### 3. Re: Don't consider zero in average calculation

Verbose is fine as long as it works...and it works perfectly! Thanks.

• ###### 4. Re: Don't consider zero in average calculation

You are looking for the Excel function AVERAGEIF(). This might be a useful FileMaker "Idea".