1 Reply Latest reply on Jul 12, 2018 5:54 AM by garth.conrad

    Null denominator in Calculated field

    carlos capella

      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!