4 Replies Latest reply on May 19, 2016 5:04 PM by Susan Baier

# Exclude responses with certain measure value from calculation

I'm struggling with a calculation to determine the percentage of respondents who rated an item with a 6 or 7 on a 7-point scale (Top 2 Box) IF a response of "8" indicated they were unable to rate the item.

My typical calculation for % Top 2 Box is this:

SUM(

IF [Value]>=6 then 1 ELSE 0 END

)

/ SUM([Number of Records])

I can't figure out how to exclude responses with an "8" from this calculation, so that the percentage calculated is among only those who didn't rate it an "8".

Thanks in advance for the help!

• ###### 1. Re: Exclude responses with certain measure value from calculation

Hi Susan,

Have you tried something like below?  I think it will exclude 8's.  You might want to exclude them from the denominator as well.

Good luck,

Ivan

SUM(

IF [Value]>=6 and [Value] != 8 then 1 ELSE 0 END

)

/ SUM([Number of Records])

• ###### 2. Re: Exclude responses with certain measure value from calculation

Hi Ivan! Thanks for your suggestion. Unfortunately, this is excluding the "8" responses in the numerator, but not removing those records from the denominator.

So, on one item (for example) the total number of responses is 347. 30 of those are "8" responses, so 317 are 1-7.

With 60 "6" responses and 147 "7" responses, my total top 2 box responses is 207.

What I'm trying to get is the percentage calculated by 207/317 (65%). This calculation gives me 207/347 (60%). It's using the right numerator, but the denominator includes all of the responses including the 8's instead of just the responses that were 1-7.

Any suggestions? Thanks again!

• ###### 3. Re: Exclude responses with certain measure value from calculation

Hi Susan,

Give this a try, it should exclude 8 from the denominator.  Let me know how it works.

Ivan

SUM(IF [Value]>=6 and [Value] != 8 then 1 ELSE 0 END)

/ SUM(IF [Value] !=8 THEN [Number of Records] ELSE 0 END)

2 of 2 people found this helpful
• ###### 4. Re: Exclude responses with certain measure value from calculation

Woohoo! You're a lifesaver, Ivan! That worked beautifully. And now I know how to do that. :-)