Sorting Ageing Bucket

Question

Hi,

I am using the below function in cell to get the desired bucket name. And after that when i do the pivot the sequence is not coming in the correct order, then  i have move the bucket in the pivot by rearranging them. After 0-30, the bucket 180+ comes which  i moved to end in the pivot. It prob due to excel read that sequence  (after  0 then 1, 2 etc). I tried with adding prefix a, b, c, to buckets, it works but looks odd in the pivot chart.

Can anyone give any solution to this what prefix or suffix should give the "180+" so that it would always comes in the last

=IF(I2>=180,"180+",IF(I2>90,"90+",IF(I2>60,"60-90",IF(I2>30,"30-60",IF(I2>=0,"0-30",0)))))

Regards,

RK

Answer 1

Hi,

I would suggest to use greater or equal to... (>=0, >=30, >=60, >=90, >=180)

now,

you can have LOOKUP function

assuming that values in cell A1

 in cell A2 write:

=IF($A$1<0,"",LOOKUP($A$1,{0,30,60,90,180},{"0-30","30-60","60-90","90+","180+"}))

or

using mathematic symbols

=IF($A$1<0,"",LOOKUP($A$1,{0,30,60,90,180},{"[0-30)","[30-60)","[60-90)","[90-180)",">=180"}))

Answer 2

I only have a fairly crude method for now:

"a: 0-30"

"b: 30-60"

"c: 60-90"

"d: 90+"

"e: 180+"

Answer 3

Hi,

Nop...> or + comes first before number:(

Rk

Answer 4

Try ">180", since the ASCII code for ">" is higher than those of numbers. "+" is lower unfortunately but even "+180" would probably work. Numbers come before any text in ascending order sorting I think.