Display FORMAT with millions with one decimal?

[AliceW]

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

[AliceW]

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

[lbendlin · Accepted solution]

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.