Forum Discussion

AliceW's avatar
AliceW
Icon for Power Participant rankPower Participant
4 years ago
Solved

Display FORMAT with millions with one decimal?

Hi all,

I am not able to use FORMAT to display an amount in millions with ONE decimal.

Displaying it with wero decimals works...

 

format([Amount],"0,, M")
I've tried quite a few changes, but it does not seem to work! Changing it to
"0.0,, M" brings in one decimal, but also brings in the entire 7 figure number...
 
Please help.
Thank you,
Alice
Need Help
  • lbendlin's avatar
    lbendlin
    4 years ago

    You can do some log math here.  The format string depends on the number of digits in your actual data point.  For example if your data value is 10000 then you need one comma. if the value is 100 then you need zero commas etc.

     

    So - take the log10 of your value, subtract 6 if you are going for millions, integer (floor) divide it by three, and then add the resulting number of commas to your format string.

2 Replies

  • AliceW's avatar
    AliceW
    Icon for Power Participant rankPower Participant
    3 years ago

    I found this solution: "#,##,,.0M"

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    4 years ago

    You can do some log math here.  The format string depends on the number of digits in your actual data point.  For example if your data value is 10000 then you need one comma. if the value is 100 then you need zero commas etc.

     

    So - take the log10 of your value, subtract 6 if you are going for millions, integer (floor) divide it by three, and then add the resulting number of commas to your format string.