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

    Exclude values outside of a Range

    Jason Straub

      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?

       

      Thank you in advance for any help you can give.