## 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!

## 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