Problem with a formula

Occasional Visitor



I am trying to write a formula which will turn any given number in a cell, for example 2034 to 2 000, 12124 to 12 100, 123456 to 123 500 and have tried using this one 

";IF(T19>0;IF(T12>1000;CONCATENATE(ROUNDDOWN(T12/1000;0);" ";MROUND((T12/1000-ROUNDDOWN(T12/1000;0))*1000;10)));MROUND(T12*-1;10));"

Only the problem that I have is that its not working correctly, for it example to turned 2000 into "2 0" and sometimes shows only "FALSE". Does anybody see a problem with the formula presented and have a solution?


Thank you in advance



1 Reply

You do realize the very 1st IF is looking at T19 not T12 right? so if T19<=0 you would get FALSE. ALSO is there a reason you don't just use
=TEXT(T12,"# ###")
you might need to add that IF() re: T19 if that is important and a MROUND(T12,10) if that is also intended

EDIT:  OK so I think I get it, the intent was for that last part with the *-1 to be part of that 1st IF() but I think you are missing the FALSE condition for the 2nd IF() statement when T12<1000.

Also, why are you adding that space??  Do you really want the result to be TEXT and NOT a number? or just for it to LOOK that way/format?  Because if you just need that "LOOK" then you should use Number Format and create a custom format ("# ###") to display those numbers that way but they can still be numbers (i.e. you can still perform math and value comparisons on those cells)