none
Nested IIF Statement Not Working RRS feed

  • Question

  • The following nested IIF statement I am trying to use does not seem to be working. The formula is as follows:

    IIf([Number3]<30,30,IIf([Number3]>=30,70,IIf([Number3]<=70,70,IIf([Number3]>70,100,30))))

    This formula should take the number in column [Number3] and populate the column I am entering this formula into accordingly. If the number in column [Number3] is less than 30, populate new column with the number 30. If the number in column [Number3] is between 30 and 70, populate new column with the number 70. If the number in column [Number3] is above 70, then populate the new column with the number 100. 

    The first two IIF statements seem to work, but the last two do not. If someone can let me know where I've gone wrong, that'd be great.

    Thank you.

    Wednesday, August 29, 2018 7:43 PM

Answers

  • alicewang96,

    You have more elements in your formula than you need. Remember the formula is evaluated from left to right so the first condition to be true sets the value. I'd try the following formula:

    IIf([Number3]<30,30,IIf([Number3]<=70,70,100))

    Hope this helps.

    John

    • Marked as answer by alicewang96 Thursday, August 30, 2018 12:56 PM
    Wednesday, August 29, 2018 10:56 PM

All replies

  • alicewang96,

    You have more elements in your formula than you need. Remember the formula is evaluated from left to right so the first condition to be true sets the value. I'd try the following formula:

    IIf([Number3]<30,30,IIf([Number3]<=70,70,100))

    Hope this helps.

    John

    • Marked as answer by alicewang96 Thursday, August 30, 2018 12:56 PM
    Wednesday, August 29, 2018 10:56 PM
  • This fixed my issue. Thanks John!
    Thursday, August 30, 2018 12:56 PM
  • alicewang96,

    You're welcome and thanks for the feedback.

    John

    Thursday, August 30, 2018 1:27 PM