Number rounding

[RSComm]

Does anyone have a function that can drop the last 3 '0's of a number and replace it with a letter?  For example:

 

$45,346 changed to $45K

$1,234.411 changed to $1,234M

 

Thanks in advance.

[LaRetta]

Here is an idea of an approach.  

Let ( [
numText = NumToJText ( num ; 1 ; 0 ) ;
commas = PatternCount ( numText ; "," )
] ;
Left ( numText ; Position ( numText ; "," ; 1 ; 1 ) - 1 )
&
Choose ( commas - 1 ; "K" ; "M" )
)

... extend this portion of the calculation out as far as you like by adding letters after the M.

BTW, when you say drop, I dropped.  If you wish for rounding you will need to make that adjustment.  :-)

NumJ.fp7.zip

[RSComm]

Thanks LaRetta,  I never noticed that function before.

[RSComm]

LaRetta,  One last question, how do I make a number over one million show as 1.23M with 2 decimal points?

Thanks again.

[Raybaudi]

Try:

Let([

num = YourNumberField ;

T = Div ( Abs ( num ) ; 10^3 ) ;

S = Round ( Abs ( num ) / 10^6 ; 2 )

];

Case(

not T ; num ;

T < 1000 ; Sign ( num ) * T & "K" ;

Sign ( num ) * S & "M"

)

)

[LaRetta]

Nice Daniele!  

 

I wanted to mention that, when posting a calculation with Let(), it messes up when emails are sent.  It is best to place the calc within Code otherwise if someone uses the email instead of coming back here to get the calculation, it won't work.

BTW, I changed my post to include my entire calculation for those still on v11.

[Lee Smith]

Hi Daniele,

 

Nice.

 

I would only add the filter to your number in case the raw data is as shown by RSComm i.e. $45,346 and $1,234.411

Let ( [
		num = Filter ( num ; "0123456789" ) ;
		T = Div ( Abs ( num ) ; 10 ^ 3 ) ;
		S = Round ( Abs ( num ) / 10 ^ 6 ; 2 )
	] ;
	Case (
		not T ;
		num ;
		T < 1000 ;
		Sign ( num ) * T & "K" ;
		Sign ( num ) * S & "M"
	)
)

[RSComm]

Thanks for all the help!

[comment]

I would suggest =

Case (
number < 10^3 ; number ;
number < 10^6 ; Round ( number / 10^3 ; 0 ) & "k" ;
Let (
n = Round ( number / 10^6 ; 2 ) ;
Int ( n ) & SerialIncrement ( ".00" ; 100*Mod ( Abs ( n ) ; 1 ) ) & "M"
)
)

The two most significant differences between this and Daniele's method can be seen when number = 1999 (1k vs. 2k) and when number = 1999999 (2M vs. 2.00M).