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.)

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

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

HTH

Lee

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 ) & "%"

) );" ") )

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.

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, 200718 yr by Guest

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 )

)

)

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

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 )

)

)

That'll work. Thank you