1.0e+9

[Stuart Taylor]

I have a calculated result of

1000000000

it ends up as:

1.0e+9

could someone explain this to me.

100000000 (fine)

1000000000 (re-formatter)

1000000001 (fine)

10000000000 (re-formatted)

I must admit to being a little confused.

I need to reformat the calculated result so 1.0e+9 is not much use to me.

best

Stuart

Edited March 23, 2009 by Guest

[comment]

What exactly is your question: why is 10^9 converted to scientific notation, or why 10^9 + 1 isn't?

[Stuart Taylor]

Hey Comment,

The first would be good, not specifically that number but why and when I can expect it to occur.

best

Stuart

PS, had a lot of fun with your psychic file.

Edited March 23, 2009 by Guest

[comment]

I don't know of any FMI-published rules, but I believe you can expect it when a number has 10 or more digits, and the trailing 9 (or leading 9 in case of a fraction) are zeros.

[Stuart Taylor]

That is good to know.

I am using a custom function to format values into the correct currency format

1,000.00 = $1,000.00 USD

so when i ended up with $1,.0e,+10.00 USD instead of $1,000,000,000,000.00 USD i was slightly put out as i could find nothing wrong with my calculation

now that would seem like an unusual amount but i may very well end up with this value being used in my solution as some of my clients have items valued in the millions so a total of that over a number of years may end up in the billions.

Any thoughts on the best way to tackle these values.

here is my current custom function.

(note the currency sign leading and/or trailing is added separately)

FormatNumber ( value ; thousandSeparator ; decimalSeparator ; precision )

Let ( [

$value = Filter ( value ; "0123456789.,+-e" ) ;

$plusminus = Case ( value < 0 ; "-" ; "" ) ;

$value = Round ( $value ; precision ) ;

$Integer = Int ( $value ) ;

$decimal = Substitute ( $value - $Integer ; "." ; "" ) ;

$paddecimal = $decimal & Choose ( precision - Length ( $decimal ) ; "" ; "0" ; "00" ; "000" ; "0000" ; "00000" ; "000000" ) ;

$IntLength = Length ( $Integer ) ;

$p1 = Right ( $Integer ; 3 ) ;

$p2 = Right ( Left ( $Integer ; $IntLength - 3 ) ; 3 ) ;

$p3 = Right ( Left ( $Integer ; $IntLength - 6 ) ; 3 ) ;

$p4 = Right ( Left ( $Integer ; $IntLength - 9 ) ; 3 ) ;

$p5 = Right ( Left ( $Integer ; $IntLength - 12 ) ; 3 ) ;

$p6 = Right ( Left ( $Integer ; $IntLength - 15 ) ; 3 ) ] ;

$plusminus &

Choose ( GetAsBoolean ( not IsEmpty ( $p6 ) ) ; "" ; $p6 & thousandSeparator ) &

Choose ( GetAsBoolean ( not IsEmpty ( $p5 ) ) ; "" ; $p5 & thousandSeparator ) &

Choose ( GetAsBoolean ( not IsEmpty ( $p4 ) ) ; "" ; $p4 & thousandSeparator ) &

Choose ( GetAsBoolean ( not IsEmpty ( $p3 ) ) ; "" ; $p3 & thousandSeparator ) &

Choose ( GetAsBoolean ( not IsEmpty ( $p2 ) ) ; "" ; $p2 & thousandSeparator ) &

Choose ( GetAsBoolean ( not IsEmpty ( $p1 ) ) ; "0" ; $p1 ) &

decimalSeparator & $paddecimal

)

Edited March 23, 2009 by Guest

[comment]

The mistake here is using text functions on a number. The number is converted to text, and the conversion does use scientific notation where it thinks appropriate.

However, this has all become obsolete ever since this cat has been let out of the bag:

http://fmforums.com/forum/showtopic.php?tid/195085/post/289577/#289577

Edited March 23, 2009 by Guest

[Stuart Taylor]

That is really great, many thanks :