Crystal division by zero using if in
Crystal division by zero using if in
(OP)
Hello -
I have a study that has 3 responses, Y, N or NA. I need to divide Y/(Y+N) to create a score. Y=1, N=0 and NA isn't used. I get the error "division by zero" and I'm not sure how to fix it using if in.
(If 'Y' in {HFF_HIM_MED_SURGS_Z_VIEW.Z1_Hp_Present_And_Complete_With_All_Elements}
then 1
else 0)
+
(If 'Y' in {HFF_HIM_MED_SURGS_Z_VIEW.Z2_Provider_Completed_Hp_In_Timely_Manner}
then 1
else 0)
/
((If 'NA' in {HFF_HIM_MED_SURGS_Z_VIEW.Z1_Hp_Present_And_Complete_With_All_Elements}
then 0
else 1)
+
(If 'NA' in {HFF_HIM_MED_SURGS_Z_VIEW.Z2_Provider_Completed_Hp_In_Timely_Manner}
then 0
else 1)
Thank you!
I have a study that has 3 responses, Y, N or NA. I need to divide Y/(Y+N) to create a score. Y=1, N=0 and NA isn't used. I get the error "division by zero" and I'm not sure how to fix it using if in.
(If 'Y' in {HFF_HIM_MED_SURGS_Z_VIEW.Z1_Hp_Present_And_Complete_With_All_Elements}
then 1
else 0)
+
(If 'Y' in {HFF_HIM_MED_SURGS_Z_VIEW.Z2_Provider_Completed_Hp_In_Timely_Manner}
then 1
else 0)
/
((If 'NA' in {HFF_HIM_MED_SURGS_Z_VIEW.Z1_Hp_Present_And_Complete_With_All_Elements}
then 0
else 1)
+
(If 'NA' in {HFF_HIM_MED_SURGS_Z_VIEW.Z2_Provider_Completed_Hp_In_Timely_Manner}
then 0
else 1)
Thank you!
Talk To Other Members
RE: Crystal division by zero using if in
Please show some samples of how these two fields appear for two or three rows of data and then what you would expect as the result for your formula for that mock data.
-LB
RE: Crystal division by zero using if in
- I need a score for each employee. Formula above works for score by employee. I would never have an NA for both numerator and denominator
- I need a score for each question. It is possible to have a NA for both numerator and denominator. This is where I get the division by zero.
- Each question must have a Y, N, or NA; no question may be left blank
Score are calculated by:
- Numerator = count all Y
- Denominator = count Y + N
- NA will always be excluded
Employee #1
Score: 100% (1/1)
Question#1: Hp_Present_And_Complete_With_All_Elements = Y
Question#2: Provider_Completed_Hp_In_Timely_Manner = NA
Employee #2
Score: 0% (0/1)
Question#1: Hp_Present_And_Complete_With_All_Elements = N
Question#2: Provider_Completed_Hp_In_Timely_Manner = NA
Employee #3
Score: 100% (1/1)
Question#1: Hp_Present_And_Complete_With_All_Elements = Y
Question#2: Provider_Completed_Hp_In_Timely_Manner = NA
Overal Scores:
Question#1: 66% (2/3)
Question#2: Here is where I get the error of division by zero
RE: Crystal division by zero using if in
-LB
RE: Crystal division by zero using if in
If only two, you could manage this with a couple of simple formulas; if many, I could show you how to manage this with arrays.
Still would like to see the layout of your data.
Skip makes a good point, but I understand why you might want to know the percentage of various answers based on the subset of those who provided answers, although it would be important to provide the response rate to provide overall context.
-LB
RE: Crystal division by zero using if in
RE: Crystal division by zero using if in
If it won't work, I will just do a score for each employee and then an overall score for all employees. These are report cards so knowing at a glance which questions brought the overall score down would be very helpful.
Thank you, Sherry
RE: Crystal division by zero using if in
-LB
RE: Crystal division by zero using if in
Is this what you intend?
Skip,
Just traded in my OLD subtlety...
for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
RE: Crystal division by zero using if in
RE: Crystal division by zero using if in
Skip,
Just traded in my OLD subtlety...
for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
RE: Crystal division by zero using if in
RE: Crystal division by zero using if in
Numerator: If Answer="Y" Then Count Answer
Denominator: If Answer<>"NA" Then Count Answer
If Denominator=0 Then 0 Else Numerator/Denominator
Bottom line: You cannot ignore NA values. You must treat them with some sort of logic.
I'm making assumptions about how to treat NA values. Maybe my assumptions are incorrect. Either you count NA values or you do not count NA values. But in all cases, if the Denominator is ZERO, it cannot be the Divisor. But NAs must be treated with some sort of coherent logic.
Skip,
Just traded in my OLD subtlety...
for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein