Decimal and rollup

[alemberg]

Hello all as you can see in the screenshot below I am getting the following number format when using AUTO. I have been asked by some folks on the team whether it is possible to maintain the same but with only one decimal in all cases, not 1 and 2 depending on the amount. Also while maintaining the K, M and G at the end.

[Anonymous]

Hi Alex, You probably might have tried this already but want to check anyway:  What happens when you select Number formatting as 'Custom' (instead of auto) and in the Format pattern text box  use this : "#,##0.0"

Tx

Nakul

[alemberg]

I tried it on the -76.97M and turned it into turned it into -76,970,634.4 and I am trying to get -76.9M (just drop the 7 and keep the M)

[jagan · Accepted solution]

Hi Alex,

If you round off to one decimal it will become -77.0M.  Try below expression.

=Num(Num#('-76.97M', '#,###.00M'), '#,###.0M')

OR

Try =Left('-76.97M', 5) & 'M'

Regards,

Jagan.

[reddy-s]

Hi Alex,

Change the auto to number and set custom properties in the format field: typpe in : #,##0.# M

This will limit it to 1 decimal only.

[alemberg]

Thank you all for your help.

[jagan]

Hi Alex,

If you got the answer close this thread by giving correct and helpful Answers to the posts which helps you in finding the answers.

Regards,

Jagan.