Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
change TEXT format to number, keep 00 neg results..
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,DI90),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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change number format from text to number? | New Users to Excel | |||
Change number (in text format) to numeric format | Excel Discussion (Misc queries) | |||
Hoe to change text format .126 to number format 0.126 | Excel Discussion (Misc queries) | |||
How to change text format .126 to number format 0.126 ? | Excel Worksheet Functions | |||
How do i change numbers in text format to number format? | New Users to Excel |