Show $ value in Thousands/Millions

Keith Dickson
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!

Answers

  • Antonio Figueroa
    Antonio Figueroa ✭✭✭✭✭✭

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

    image.png

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

    doesn't work for me.