cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
RuanG Frequent Visitor
Frequent Visitor

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
1 ACCEPTED SOLUTION

Accepted Solutions
Nishantjain Member
Member

Re: IF Statement not working with Commas (Decimals)

@RuanG 

 

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 5
rocky09 Established Member
Established Member

Re: IF Statement not working with Commas (Decimals)

Can you please share some sample data?

Nishantjain Member
Member

Re: IF Statement not working with Commas (Decimals)

@RuanG 

 

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
RuanG Frequent Visitor
Frequent Visitor

Re: IF Statement not working with Commas (Decimals)

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

RuanG Frequent Visitor
Frequent Visitor

Re: IF Statement not working with Commas (Decimals)

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

Pauwnrajpp Member
Member

Re: IF Statement not working with Commas (Decimals)

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