2 Replies Latest reply on Mar 15, 2016 7:49 AM by Jason Straub

# Exclude values outside of a Range

Hello Everyone,

I am working with grade point average data from high school transcripts. My data set has a lot of zero values and then some values that are above 4. I am able to exclude zeros using this calculated field:

AVG(IIF([GPA]=0,NULL,[GPA])

So excluding zeroes is working, but I have a handful of GPA's that have very large values that haven't been converted to the 4.0 scale (values like 96, etc.). Rather than individually covert each one not knowing its original scale, I just want exclude everything above 4. I tried modifying my calculated field, but can't get the conditional logic correct:

AVG(IIF([GPA]=0,NULL,[GPA]) OR IIF([GPA]>4,NULL,[GPA]))

Any suggestions?

• ###### 1. Re: Exclude values outside of a Range

Jason - IF AVG([GPA]) >= 1 and AVG([GPA]) <= 4 then AVG([GPA]) ELSE NULL END

Should work fine. Let me know if it doesn't.

• ###### 2. Re: Exclude values outside of a Range

Hi Pooja,

Thank you for the help. The formula you gave me didn't want to filter out the zeros, but it lead me to the correct logic. I changed the field to this:

IF [GPA] > 0  AND([GPA]<= 4 THEN [GPA] ELSE NULL END

Then I drug the pill into the worksheet and set the aggregation calculation for AVG. Presto! All fixed now.

Thank you