Smartsheet Product Feedback & Ideas

Smartsheet Product Feedback & Ideas

Help shape the future of Smartsheet! Share your feature wishlist and improvement requests here.

Sign in to submit new ideas and vote
Get Started

Negative Number Formatting in Parenthesis

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

  • ✭✭✭✭✭

    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.

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

Trending Ideas