Text function; Automatical conversion to scientific notation

RalfdeGier
RalfdeGier
in Modeling

Hy everyone!
I'm encountering a problem with converting my values to text with the TEXT() function.

My problem:

 

When the value is 10,000,000 and I use the text() function, there is no problem. The text line item will show "10.000.000". 
When the value is 10,000,000.15 (with 2 decimals <> 0), The text line item wil show '1.e7' instead of  "10,000,000.15"

This causes problems with my export to other systems. Does anyone of you has encounterd the same problem /has found a workaround?

Kind regards,
Ralf 

Tagged:

Answers

  • Egor
    Egor

    Hi Ralf,

     

    If in case when the value is with 10,000,000.15 you still want to see 10000000 in your text filed, the easiest workaround would be a ROUND function:

     

    TEXT(ROUND('Your number', 0))

     

    If you want to see the decimas in text field as well, it is a bit more compex, but also possible via concatination.

     

    Best regards,

    Egor.

  • PaulRitner
    PaulRitner

    This is an odd error...!

     

    I was playing with converting 10000000.15 to TEXT and arrived at the following formula using Egor's concatination suggestion (which looks like the only way to get the whole value, with decimals, converted):

    TEXT(ROUND(Amount, 0)) & "." & MID(TEXT(ABS(ROUND(Amount, 2) - ROUND(Amount, 0))), 3, 2)

     

    Stranger yet was that, when converting the decimal, the .15 had a strange residual value 10 or so places out... even though the text input was keyed in as 10000000.15

     

    Question:  Is the export format other than CSV?  If you are exporting to a csv/flat file, then there's no need to convert the value to text prior to export (because it will become a text format in the export file).

  • Hayk
    Hayk

    This error comes from calculations. 

     

    Calculation goes crazy with some values. If you try to multiply 10000000.15 by 1, anaplan shows 1.000000015E7 (61.2 * 3 gives 183.60000000000002, but 61.2*10*3/10 = 61.2).

     

    I assume it can be somehow related to floating-point arithmetic.

  • RalfdeGier
    RalfdeGier
    Dear Paul,

    THank you for the formula, I will give it a go!

    In order to export to other systems, i need to export in CSV. The problem is that I need to have a semicolon as a seperator, instead of a comma. As stated in an ealier topic, I need to convert everything to a string text item which then used a semicolon instead of a comma. Therefore I need to transfer all values to text.

    That's the reason I have encountered this error in Anaplan!
  • abarnett
    abarnett

    Thank you for this! I use a variant for my particular use case due to the last value sometimes showing as a period with the above formula. It seems to work with all real world numbers I encounter (though there are some extremely large numbers I have tested that still do not work:
    IF
    RIGHT(TEXT(ROUND(Amount, 0)) & "." & MID(TEXT(ROUND(Amount, 6) - ROUND(Amount, 0)), 3, 6), 1) = "."
    OR LEFT(RIGHT(TEXT(ROUND(Amount, 0)) & "." & MID(TEXT(ROUND(Amount, 6) - ROUND(Amount, 0)), 3, 6), 3), 1) = "E"
    THEN
    TEXT(ROUND(Amount, 0))
    ELSE
    LEFT(TEXT(ROUND(Amount, 6)), LENGTH(TEXT(ROUND(Amount, 0))) + 7)

  • rob_marshall
    rob_marshall

    @abarnett ,

     

    Please break that up into multiple line items as a) performance will be better and b) you will actually be able to figure out what is not working.

     

    Thanks,

     

    Rob

Categories