Struggling with a IF THEN ELSEIF Formula

[Matt_Sinker]

Hello All,

I am struggling with a IF formula, basically I am wanting to sort some data into groups based on their relative rank. The groups will be splitting the data into 10% segments for further analysis, I have tried with the following and am receiving no errors, however when the data outputs this formula every record is part of the first group "0%-10%",

I hope I have explained this correctly, and someone can help me!

[afv2688 · Accepted solution]

Well you almost got the formula correctly:

IF [Rank] >=0 AND [Rank] < 0.1 THEN "0%-10%"
ELSEIF [Rank] >=0.1 AND [Rank] < 0.2 THEN "10%-20%"
ELSEIF [Rank] >=0.2 AND [Rank] < 0.3 THEN "20%-30%"
ELSEIF [Rank] >=0.3 AND [Rank] < 0.4 THEN "30%-40%"
ELSEIF [Rank] >=0.4 AND [Rank] < 0.5 THEN "40%-50%"
ELSEIF [Rank] >=0.5 AND [Rank] < 0.6 THEN "50%-60%"
ELSEIF [Rank] >=0.6 AND [Rank] < 0.7 THEN "60%-70%"
ELSEIF [Rank] >=0.7 AND [Rank] < 0.8 THEN "70%-80%"
ELSEIF [Rank] >=0.8 AND [Rank] < 0.9 THEN "80%-90%"
ELSEIF [Rank] >=0.9 AND [Rank] < 1 THEN "90%-100%"
ELSE "NA"
ENDIF

should work like this

Cheers

[Matt_Sinker]

Perfect thanks!

Almost worked first time, there was an extra AND in the first line of your answer! haha

Thanks so much, thats been annoying me for the best part of an hour. 

[Matt_Sinker]

No there wasn't, ignore me, its my poor pasting skills!

[afv2688 · Accepted solution]

Yes there was,I did correct it after haha. The struggle of typing code directly on the web XD.

Anyways, I think it would be prettier maybe like this:

IF [Rank] < 0 OR [Rank] > 1 THEN "NA"
ELSEIF [Rank] < 0.1 THEN "0%-10%"
ELSEIF [Rank] < 0.2 THEN "10%-20%"
ELSEIF [Rank] < 0.3 THEN "20%-30%"
ELSEIF [Rank] < 0.4 THEN "30%-40%"
ELSEIF [Rank] < 0.5 THEN "40%-50%"
ELSEIF [Rank] < 0.6 THEN "50%-60%"
ELSEIF [Rank] < 0.7 THEN "60%-70%"
ELSEIF [Rank] < 0.8 THEN "70%-80%"
ELSEIF [Rank] < 0.9 THEN "80%-90%"
ELSE "90%-100%"
ENDIF

Cheers!

[Matt_Sinker]

Even better!

Looks prettier, as you say, also the original solution was returning 1 Null record in NA, which is not present now, also the split between the groups now makes more sense, with an even SKU split per group, as I expected to see.

Thanks again.