
1. Re: Formatting numbers to American short form
joedu Aug 23, 2011 8:47 AM (in response to joedu)Nevermind! Got it.... simple, and overthought, as usual ;)
"$" &
Case (
Length (Number) =7 ; Round ( Number ; 4 ) / 1000000;
Length (Number) =8 ; Round ( Number ; 5 ) / 1000000;
Length (Number) =9 ; Round ( Number ; 6 ) / 1000000;
Length (Number) =10 ; Round ( Number ; 7 ) / 1000000000;
Length (Number) =11 ; Round ( Number ; 8 ) / 1000000000;
Length (Number) =12 ; Round ( Number ; 9 ) / 1000000000;
)
&
Case (
Length (Number) > 6 and Length (Number) <10 ; " million" ;
Length (Numbert) > 9 and Length (Number) <13 ; " billion" ;
Length (Number) > 12 and Length (Number) <16 ; " trillion"
)Cheers!

2. Re: Formatting numbers to American short form
joedu Aug 23, 2011 12:25 PM (in response to joedu)As a quick followup, the rounding amount (4, 5, etc) dictates the number of decimals. I needed to have only 1 place after the decimal in every instance, so in the millions, the rounding had to be 5, and for billions, 8, etc. Thus the calculation could be simplified to just this:
++++++
"$" &
Case (
Length (Number) > 6 and Length (Number) < 10 ; Round ( Number ; 5 ) / 1000000;Length (Number) > 9 and Length (Number) < 13 ; Round ( Number ; 8 ) / 1000000000;
Length (Number) > 12 and Length (Number) < 16 ; Round ( Number ; 8 ) / 1000000000000;
)
&
Case (
Length (Number) > 6 and Length (Number) <10 ; " million" ;
Length (Numbert) > 9 and Length (Number) <13 ; " billion" ;
Length (Number) > 12 and Length (Number) <16 ; " trillion"
)+++++++
So,
The number "1234567" results in "$1.2 million"
The number "12345678" results in "$12.3 million"
Etc. It will round up so that if the 2nd place after the decimal is a 5 or larger, the first decimal place will round up. So 1250000 would result in "$1.3 million."