N
nastech
hi, trying to fix a formula used for sorting columns, as a master sort.
works as is, but inconvienient moving forward on formatting / sorting error:
sort numbers and numbers as text separately.
issue needs to keep "00" or double 00 (etc..) for negative result lines, so
item can sort against other lines. is there a way to change away from using
text numbers?
long example, but just need to look at the 3 TEXT() entries, thanks.
note: 1st TEXT() item recipicated for a reverse sort. if applicable, could
not get to work, a negative -() result for ascending sort on a negative
number / hence reverse sort.
=IF($DI$2=2,DR9,99)&
IF(AND($DI$2=1,D9=0,DI9>0),TEXT(1/(DI9*10^-9),"0000000"),9995555)&
IF($AN$2=1,AO9,9)&
IF($BX$2=1,TEXT(BX9,"00"),99)&
IF(AND($BT$2=1,BT9<>""),TEXT(ROUND(BT9,0),"00"),99)&
IF(AND($BW$2=1,BW9<>{"-",""}),0,9)&
IF(AND($BS$2=1,BS9<>{"-",""}),0,9)&
IF(AND($BO$2=1,BO9<>{"-",""}),0,9)&
IF($BH$2=1,BH9,9)&
IF(AND($AA$2=1,F9=1),"xx",IF($AA$2=1,AA9,9))&
IF($BB$2=1,BB9,9)&
IF($BE$2=1,BE9,9)&
IF(BZ9="",9,BZ9)
works as is, but inconvienient moving forward on formatting / sorting error:
sort numbers and numbers as text separately.
issue needs to keep "00" or double 00 (etc..) for negative result lines, so
item can sort against other lines. is there a way to change away from using
text numbers?
long example, but just need to look at the 3 TEXT() entries, thanks.
note: 1st TEXT() item recipicated for a reverse sort. if applicable, could
not get to work, a negative -() result for ascending sort on a negative
number / hence reverse sort.
=IF($DI$2=2,DR9,99)&
IF(AND($DI$2=1,D9=0,DI9>0),TEXT(1/(DI9*10^-9),"0000000"),9995555)&
IF($AN$2=1,AO9,9)&
IF($BX$2=1,TEXT(BX9,"00"),99)&
IF(AND($BT$2=1,BT9<>""),TEXT(ROUND(BT9,0),"00"),99)&
IF(AND($BW$2=1,BW9<>{"-",""}),0,9)&
IF(AND($BS$2=1,BS9<>{"-",""}),0,9)&
IF(AND($BO$2=1,BO9<>{"-",""}),0,9)&
IF($BH$2=1,BH9,9)&
IF(AND($AA$2=1,F9=1),"xx",IF($AA$2=1,AA9,9))&
IF($BB$2=1,BB9,9)&
IF($BE$2=1,BE9,9)&
IF(BZ9="",9,BZ9)