none
Using > and < in an If statement RRS feed

  • Question

  • Hi,

    I am trying to get an if statement to label up a column based on the value of another. I need to look at the numbers if they are greater than or less than, but I get an error on < in the formula below. 

    Can anyone help me resolve this?

    = If([LOS Months Continuous]<6,"6 Months",If([LOS Months Continuous]>5 and <13,"6-12 Months",If([LOS Months Continuous]>12 and <25,"1-2 Years",If([LOS Months Continuous]>24 and <37,"2-3 Years",If([LOS Months Continuous]>36 and <49,"3-4 Years",If([LOS Months Continuous]>48 and <61,"4-5 Years","5+ Years"))))))

    The error highlights the < next to the 13 in line one.

    Many Thanks.

    Tuesday, May 16, 2017 12:44 PM

Answers

  • If you want to check on 2 values, then you need to repeat the field name.

    Otherwise you can't just put a bunch of if's in a row.

    The correct code would be:

    if [LOS Months Continuous]<6 then "6 Months"
    else if [LOS Months Continuous] <13 then "6-12 Months"
    else if [LOS Months Continuous]<25 then "1-2 Years"
    else if [LOS Months Continuous]<37 then "2-3 Years"
    else if [LOS Months Continuous]<49 then "3-4 Years"
    else if[LOS Months Continuous] <61 then "4-5 Years"
    else "5+ Years"

    Tuesday, May 16, 2017 2:54 PM

All replies

  • A few things..

    - no need for commas and parentheses

    - "if", "else" "and" and "then" are all lowercase

    - after your "and" you need to explicitly call the column again

    the following should work :)

    =if

    [LOS Months Continuous]<6 then "6 Months"

    else if [LOS Months Continuous]>5 and [LOS Months Continuous] <13 then "6-12 Months"

    else if [LOS Months Continuous]>12 and [LOS Months Continuous] <25 then "1-2 Years"

    else if [LOS Months Continuous]>24 and [LOS Months Continuous] <37 then "2-3 Years"

    else if [LOS Months Continuous]>36 and [LOS Months Continuous] <49 then "3-4 Years"

    else if [LOS Months Continuous]>48 and [LOS Months Continuous] <61 then "4-5 Years"

    else "5+ Years"

    Regards

    Laurence

    • Proposed as answer by MarcelBeug Tuesday, May 16, 2017 3:09 PM
    Tuesday, May 16, 2017 2:45 PM
  • If you want to check on 2 values, then you need to repeat the field name.

    Otherwise you can't just put a bunch of if's in a row.

    The correct code would be:

    if [LOS Months Continuous]<6 then "6 Months"
    else if [LOS Months Continuous] <13 then "6-12 Months"
    else if [LOS Months Continuous]<25 then "1-2 Years"
    else if [LOS Months Continuous]<37 then "2-3 Years"
    else if [LOS Months Continuous]<49 then "3-4 Years"
    else if[LOS Months Continuous] <61 then "4-5 Years"
    else "5+ Years"

    Tuesday, May 16, 2017 2:54 PM
  • Thank you for your help, much appreciated.
    Wednesday, May 17, 2017 7:48 AM
  • Thanks again for helping me out.

    It's all learning at this stage, but it's good fun.

    Wednesday, May 17, 2017 7:49 AM