Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

IF Statement not working with Commas (Decimals)

Good day,

 

I am encountering an issue that I believe has something to do with my Locale settings.  Nonetheless I have not yet figured out the reason why this is not working.  I am doing a calculation that results in a decimal number (0,72).

 

The issue I am having is with this below formula, it is not returning the correct Text.  It should be seen as "Strong" however it returns "Moderate"

 

Formula (The CC is the name of the variable that returns the 0.72 as mentioned before) 

 

Please keep in mind that I have tried using full stops (0.30) but it gives an error in the Formula bar.

 

IF(CC <= {0,30},"Weak",
IF(CC > {0,30} || CC <= {0,50},"Moderate",
IF(CC > {0,50} || CC <= {0,99},"Strong",
IF(CC= {1},"Perfect","No")
)
)
)
Please could someone assist ! Thanks in advance
Need Help
  • Nishantjain's avatar
    Nishantjain
    7 years ago

    Anonymous 

     

    You need to replace the "||" with "&&". "||" is an alternate for "Or".

     

    In your formula , if the value is more than 0,30 or less than 0,50, that will give "Moderate" and 0,72 is more than 0,30.

     

    I would suggest you change your formula as below 

     

    Option 1:

    IF (
    CC <= { 0, 30 },
    "Weak",
    IF (
        CC > { 0, 30 }
            && CC <= { 0, 50 },
        "Moderate",
        IF (
            CC > { 0, 50 }
                && CC <= { 0, 99 },
            "Strong",
            IF ( CC = { 1 }, "Perfect", "No" )
        )
    )
)
    Option 2:
    Switch is a better way to write nested "Ifs". See if this makes sense
     
    SWITCH (
    TRUE (),
    CC = 1, "Perfect",
    CC > 0,50, "Strong",
    CC > 0,30, "Moderate",
    CC <= 0,30, "Weak",
    "No"
)
    Thanks

5 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Unfortunately not - Its a large dataset in order to calculate the correlation coeficient

  • Nishantjain's avatar
    Nishantjain
    Icon for Continued Contributor rankContinued Contributor
    7 years ago

    Anonymous 

     

    You need to replace the "||" with "&&". "||" is an alternate for "Or".

     

    In your formula , if the value is more than 0,30 or less than 0,50, that will give "Moderate" and 0,72 is more than 0,30.

     

    I would suggest you change your formula as below 

     

    Option 1:

    IF (
    CC <= { 0, 30 },
    "Weak",
    IF (
        CC > { 0, 30 }
            && CC <= { 0, 50 },
        "Moderate",
        IF (
            CC > { 0, 50 }
                && CC <= { 0, 99 },
            "Strong",
            IF ( CC = { 1 }, "Perfect", "No" )
        )
    )
)
    Option 2:
    Switch is a better way to write nested "Ifs". See if this makes sense
     
    SWITCH (
    TRUE (),
    CC = 1, "Perfect",
    CC > 0,50, "Strong",
    CC > 0,30, "Moderate",
    CC <= 0,30, "Weak",
    "No"
)
    Thanks
    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thanks Option 2 worked well, I just needed to incase the decimals values in {} brackets, thanks for your help !

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi RuanG,

     

    You can use switch statement to check the condition of this statement.

     

    Like,

     

    CC_Status = SWITCH(TRUE(), PBI_Data[CC] <= 30, "WEAK", AND(PBI_Data[CC] > 30, PBI_Data[CC] <50), "MODERATE", AND(PBI_Data[CC] > 50, PBI_Data[CC] < 99), "STRONG", PBI_Data[CC] = 1, "PERFECT", "NO")
     
    This would helps you a lot.
     
    Regards,
    Pradeep