Sign in to submit new ideas and vote
Get Started

Negative Number Formatting in Parenthesis

Jake Gustafson
Jake Gustafson Community Champion

As a Smartsheet user, I want a standard numbering format to display negative numbers in parenthesis with proper thousands/millions/etc. commas and currency symbols, in order to visually display negative numbers in more of an accounting style rather than the standard negative symbol.

I've had multiple colleagues ask if there were ways to do this. The following formula is about as good as we've gotten, but that is lacking the proper comma separator to display larger values properly.

  • =IF(column@row >= 0, "($" + ABS(column@row) + ")", "($)" + ABS(column@row))

Unless someone has a surefire solution, I'm thinking this would be a nice feature to display things differently with a 'click of a button.'

Tags:
7
7 votes

Idea Submitted · Last Updated

Comments

  • Cathy Salscheider
    Cathy Salscheider ✭✭✭✭✭

    This could be connected to the request to have a numeric only column type. Then standard numeric formatting could also be implemented at the same time.

  • Upvoting this request for an option to automatically format negative numbers with parentheses instead of the negative symbol (while keeping the comma separators and the option to include a currency symbol). This is a standard, easier to read format for finance and accounting documents. For example, it would show ($50,000) instead of -$50,000.

  • Paul Newcome
    Paul Newcome Community Champion

    This would go in a helper column for display only. It outputs a text string, so if you have to run calcs on the numbers, you'll want to reference the original column. This also only works as long as your number is less than 1 trillion. There may be a more efficient formula that can be written, but I had to come up with this one in a hurry for a client. It works though, so I haven't spent any more time on it yet.

    =IF([Total Column]@row < 0, "(", "") + "$" + IF(LEN(INT(ABS([Total Column]@row))) <= 3, RIGHT(INT(ABS([Total Column]@row)), 3), IF(LEN(INT(ABS([Total Column]@row))) <= 6, IFERROR(IFERROR(MID(INT(ABS([Total Column]@row)), LEN(INT(ABS([Total Column]@row))) - 5, 3), LEFT(INT(ABS([Total Column]@row)), LEN(INT(ABS([Total Column]@row))) - 3)), "") + "," + RIGHT(INT(ABS([Total Column]@row)), 3), IF(LEN(INT(ABS([Total Column]@row))) <= 9, IFERROR(IFERROR(MID(INT(ABS([Total Column]@row)), LEN(INT(ABS([Total Column]@row))) - 8, 3), LEFT(INT(ABS([Total Column]@row)), LEN(INT(ABS([Total Column]@row))) - 6)), "") + "," + IFERROR(IFERROR(MID(INT(ABS([Total Column]@row)), LEN(INT(ABS([Total Column]@row))) - 5, 3), LEFT(INT(ABS([Total Column]@row)), LEN(INT(ABS([Total Column]@row))) - 3)), "") + "," + RIGHT(INT(ABS([Total Column]@row)), 3), IF(LEN(INT(ABS([Total Column]@row))) <= 12, IFERROR(IFERROR(MID(INT(ABS([Total Column]@row)), LEN(INT(ABS([Total Column]@row))) - 11, 3), LEFT(INT(ABS([Total Column]@row)), LEN(INT(ABS([Total Column]@row))) - 9)), "") + "," + IFERROR(IFERROR(MID(INT(ABS([Total Column]@row)), LEN(INT(ABS([Total Column]@row))) - 8, 3), LEFT(INT(ABS([Total Column]@row)), LEN(INT(ABS([Total Column]@row))) - 6)), "") + "," + IFERROR(IFERROR(MID(INT(ABS([Total Column]@row)), LEN(INT(ABS([Total Column]@row))) - 5, 3), LEFT(INT(ABS([Total Column]@row)), LEN(INT(ABS([Total Column]@row))) - 3)), "") + "," + RIGHT(INT(ABS([Total Column]@row)), 3))))) + IF([Total Column]@row - INT([Total Column]@row) = 0, ".00", RIGHT([Total Column]@row - INT([Total Column]@row), 3)) + IF([Total Column]@row < 0, ")", "")