Show $ value in Thousands/Millions

Keith Dickson

Hello, does anyone know if it is possible to display a number as a decimal value instead of the full number?

ie. instead of displaying $7,500,000 display as $7.5 M, or even just $7.5 with a column title indicating millions?

Thanks!

Antonio Figueroa

Yes there is a way using helper columns. Set your sheet up using these column labels

Then in each column copy the formulas below and paste them in the cell in that column

Len =LEN([Dollar Amount]@row)

Helper1 =IF(Len@row > 8, LEFT([Dollar Amount]@row, 4), IF(Len@row > 7, LEFT([Dollar Amount]@row, 3), IF(Len@row > 6, LEFT([Dollar Amount]@row, 2), IF(Len@row = 6, LEFT([Dollar Amount]@row, 1)))))

Helper2 =LEN([Helper1]@row)

Helper3 =IF([Helper2]@row > 3, LEFT([Helper1]@row), "")

Helper4 =IF([Helper2]@row = 4, MID([Helper1]@row, 2, 1), IF([Helper2]@row = 3, LEFT([Helper1]@row, 1)))

Helper5 =IF([Helper2]@row = 4, MID([Helper1]@row, 3, 1), IF([Helper2]@row = 3, MID([Helper1]@row, 2, 1), IF([Helper2]@row = 2, LEFT([Helper1]@row, 1), "")))

Helper6 =RIGHT([Helper1]@row)

Converted ="$" + [Helper3]@row + [Helper4]@row + [Helper5]@row + "." + [Helper6]@row + " " + "M"

And Now When you change the amount in the Dollar Amount Column it will change the converted column.

This is only going to convert the from $0.1 M to $999.9 M

Please let me know if this works for you

Keith Dickson

Thanks Antonio! Appreciate your help - we found a slightly different solution that doesn't use helper columns and adds the K or M to the column as well... here's the formula that we came up with (where initial capital allocations ins the name of the column.

=IF([Initial Capital Allocations]@row >= 1000000, [Initial Capital Allocations]@row / 1000000, IF([Initial Capital Allocations]@row >= 1000, [Initial Capital Allocations]@row / 1000000, [Initial Capital Allocations]@row + IF([Initial Capital Allocations]@row >= 1000000, "M", IF([Initial Capital Allocations]@row >= 1000000, "K", ""))))

Eliot

This does not seem to work when using SUM function (of two columns) or even referencing a field that already SUMMED two columns. Any ideas?

Mia

doesn't work for me.