2 Replies Latest reply on Aug 23, 2011 12:25 PM by joedu

    Formatting numbers to American short form

    joedu

      Title

      Formatting numbers to American short form

      Post

      I've searched and have not found an discussion on formatting numbers to the "American short form." Basically this takes a number like "32345700" and returns "32.3 million". Seems like there should be either a custom function or script for this, but haven't come across it. Any help?

        • 1. Re: Formatting numbers to American short form
          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

            As a quick follow-up, 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."