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!
The problem with your calculation is two fold, nulls and order of operation.
your values will be:
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