Crystal division by zero using if in

[Sherry B]

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!

 

[lbass]

Logically each field can only contain one of those three responses per row of data, but your formula suggests otherwise. I wonder whether what you are really trying to do is summarize across a set of rows.

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

 

[Sherry B]

Hello – I hope this makes sense as I am new to Crystal.
- 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

 

[lbass]

Please place the fields you are working with, e.g., employee, question, answer, in the detail section and then show how the fields display for three rows.

-LB

 

[lbass]

Are there really only two questions or are they many questions?

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

 

[Sherry B]

I could not get my example to paste into this section so I'm attaching a file.

 

[Sherry B]

There are over 40 questions in the study.

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

 

[lbass]

I'd like to help, but you need to show a sample of the data as requested. It MATTERS how it appears in the detail section. At the very least, you need to clarify the following: Specifically, does each question have its own field? Does each employee appear in only one row?

-LB

 

[SkipVought]

Here's a little report on percent Y ignoring NA and percent NA.

Is this what you intend?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[Sherry B]

Skip - Thank you for this. This is exactly how I need to calculate my scores! On my report, I would only show the percentage of Y, I do not need to show the percentage of NA. Thank you!

 

[SkipVought]

You didn't even notice that there is no "error of division by zero" in Q2!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[Sherry B]

Skip - Yes, I did notice it worked! Once I confirmed, was hoping you could show me the magic that made it work; sadly, I have no magic in this instance. LOL

 

[SkipVought]

Well I can tell you FUNCTIONALLY what I did, but I'm not a CR guy, so can't give you a CR formula.

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein