IF Statement not working with Commas (Decimals)

[Anonymous]

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

[Anonymous]

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

[Nishantjain · Accepted solution]

@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]

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

[rocky09]

Can you please share some sample data?

[Anonymous]

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