Show $ value in Thousands/Millions

Options

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

  • Antonio Figueroa
    Antonio Figueroa ✭✭✭✭✭✭
    Options

    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
    Options

    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
    Eliot ✭✭
    Options

    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
    Mia ✭✭
    Options

    doesn't work for me.