×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Crystal division by zero using if in

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!

RE: Crystal division by zero using if in

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

RE: Crystal division by zero using if in

(OP)
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

RE: Crystal division by zero using if in

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

RE: Crystal division by zero using if in

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

RE: Crystal division by zero using if in

(OP)
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

RE: Crystal division by zero using if in

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

RE: Crystal division by zero using if in

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



Is this what you intend?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Crystal division by zero using if in

(OP)
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!

RE: Crystal division by zero using if in

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Crystal division by zero using if in

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

RE: Crystal division by zero using if in

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close