# Null denominator in Calculated field

Hi,

I have 3 different variables with values between 1 and 7.

I want to calculate the number of >=6 divided by the sum of >=6 AND <=3 for all columns. When I have positive values for both the numerator and denominator, the formula works. But in the below scenario it doesn't:

column1     column 2     column3

6                  7                    6

5                  6                    7

7                  7                    7

6                  6                    6

The formula gives an error because the numerator is 0.

The calculated field looks like this:

((SUM(IF [column1] >=6 THEN 1 END)/(SUM(IF [column1] >=6 THEN 1 END)+SUM(IF [column1] <=3 THEN 1 END))

+

SUM(IF [column2] >=6 THEN 1 END)/(SUM(IF [column2] >=6 THEN 1 END)+SUM(IF [column2] <=3 THEN 1 END))

+

SUM(IF [column3] >=6 THEN 1 END)/(SUM(IF [column3] >=6 THEN 1 END)+SUM(IF [column3] <=3 THEN 1 END)))/3)*100

How can I fix it? I have tried with "ELSE 0" inside the formula, but it doesn't work.

Thanks!

• ###### 1. Re: Null denominator in Calculated field

The problem with your calculation is two fold, nulls and order of operation.

null/null = null

1/null = null

null/1 = null

1/1 = 1

use:

Zn(Sum(IF ([column1]>= 6) then 1 end) / Sum(IF ([column1]>= 6) OR ([column1]<=3) then 1 end))

G