Show $ value in Thousands/Millions
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!
Answers

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

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", ""))))

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?

doesn't work for me.