3 Replies Latest reply on May 26, 2016 8:26 AM by Bill Lyons

    IF THEN statement inside an IF THEN statement - Part II

    Diogo Braga

      This discussion is a continuation of: IF THEN statement inside an IF THEN statement

       

      @Bill Lyons answered my question correctly, but I am still not getting the results I need. The conditional formatting is applying to each question, which is unusual since the participation rate is for entire survey admin so it's the same for all the question.

       

      My goal is to color code (red,yellow, green, blue) results if participation is greater than 40%, if not show 'grey'. So in the example below, only Fall 14-15 should be color coded, all others should show grey.

       

      participation.png

       

      When I apply this formula, which is the one I think should work:

       

      IF SUM([Participation Rate]) > .4 THEN

       

      IF [Strongly Agree %] < 0.2 THEN 'Red'

      ELSEIF [Strongly Agree %] < 0.4 THEN 'Yellow'

      ELSEIF [Strongly Agree %] < 0.6 THEN 'Green'

      ELSEIF [Strongly Agree %] > 0.6 THEN 'Blue'

      ELSE 'NULL'

      END

       

      ELSE 'Grey'

      END

       

      I get this result:

      .4.png

       

      When I apply this formula, for testing since participation rate shouldn't be over 100%:

       

      IF SUM([Participation Rate]) > 1 THEN

       

      IF [Strongly Agree %] < 0.2 THEN 'Red'

      ELSEIF [Strongly Agree %] < 0.4 THEN 'Yellow'

      ELSEIF [Strongly Agree %] < 0.6 THEN 'Green'

      ELSEIF [Strongly Agree %] > 0.6 THEN 'Blue'

      ELSE 'NULL'

      END

       

      ELSE 'Grey'

      END

       

      I get this result:

      2016-05-25_0850.png

       

      When I apply this formula, for more testing:

       

      IF SUM([Participation Rate]) > 2 THEN

       

      IF [Strongly Agree %] < 0.2 THEN 'Red'

      ELSEIF [Strongly Agree %] < 0.4 THEN 'Yellow'

      ELSEIF [Strongly Agree %] < 0.6 THEN 'Green'

      ELSEIF [Strongly Agree %] > 0.6 THEN 'Blue'

      ELSE 'NULL'

      END

       

      ELSE 'Grey'

      END

       

      I get this result:

      2016-05-25_0908.png