Formatting Decimal Places

[Robert Clark]

Please help if you can. I need to format some numbers with a calculation. I want numbers greater than 100 to show as whole numbers, which I can do with the Int or Round function, but I also need numbers less that 100 to display to the hundredths place. It needs to show two and only two places to the right of the decimal regardless of how precisely the number is entered (i.e. 1.006% should be 1.01%; $7 should be $7.00; and $105.67 should be $105.)

[Lee Smith]

Have you tried the formatting that is available for number fields in Layout Mode?

Layout Mode >> Select Field(s) >> Format >> Number

HTH

Lee

[Robert Clark]

Yes, but the formatting needs to change based upon the amounts, as well as whether it represents money, percentages, etc. Here is what I've come up with so far, but I can't get the decimal places portion.

Case ( benchmark_unit = "#" ; Let ( [

len = Max ( 2 ; Length ( Mod ( benchmark_target; 1 ) ) - 1 )

] ;

Round ( benchmark_target;1 )) ) &

Case ( benchmark_unit = "$" ; If(not IsEmpty (benchmark_target ); Let ( [

len = Max ( 2 ; Length ( Mod ( benchmark_target ; 1 ) ) - 1 )

] ;

"$" &

Int ( benchmark_target )

);" ") ) &

Case ( benchmark_unit= "%" ; If(not IsEmpty (benchmark_target);( Let ( [

len = Max ( 2 ; Length ( Mod ( benchmark_target; 1 ) ) - 1 )

] ;

Int ( benchmark_target )

& "." &

Right ( 10^len & benchmark_target * 10^len ; len ) & "%"

) );" ") )

[comment]

You might want to try something along the lines of:

Let ( [

p = 2 * ( number < 100 ) ;

r = Round ( number ; p )

] ;

Int ( r )

& "." &

Right ( "00" & Round ( r * 100 ; 0 ) ; 2 )

)

This doesn't deal with the placement of the unit, but that should be relatively easy to add. Note also that only non-negative numbers will be processed correctly.

[Robert Clark]

Thank you very much for your help. I've edited my calculation accordingly, and it is responding almost the way I intended except for one thing.

For the numbers that I can show to the hundredths place, I don't want to show .00, but the actual value (.67, .70, .99, etc.). I am playing around with the mod function, but so far I haven't come up with the solution.

Edited January 9, 2007 by Guest

[comment]

Now I am confused. I thought you wanted precision of 2 decimal places if the number is below 100, and that's what it does. If you want to drop the ".00" for numbers above 100, try:

Let ( [

p = 2 * ( number < 100 ) ;

r = Round ( number ; p )

] ;

Int ( r )

& Case ( r < 100 ;

"." &

Right ( "00" & Round ( r * 100 ; 0 ) ; 2 )

)

)

[Robert Clark]

I apologize; I messed up the syntax. It's working now. Is there a way to deal with negative numbers in this scenario?

[comment]

Something like this, perhaps?

Let ( [

a = Abs ( number ) ;

p = 2 * ( a < 100 ) ;

r = Round ( a ; p )

] ;

Case ( number < 0 ; "-" ) &

Int ( r ) &

Case ( r < 100 ;

"." &

Right ( "00" & Round ( r * 100 ; 0 ) ; 2 )

)

)

[Robert Clark]

That'll work. Thank you