Summing data and applying parenthesis to negative value.

I need to sum 3 pieced of data, subtract a value and then show the value in a parenthesis for any that equal a negative value.

I have seen a post about this formula, but how do I use this with the the other sum function:

=VALUE(MID(ColumnName@row, 2, 10))

My formula:

=SUM([Financials 2]@row:[Financials 4]@row) - [Financials 1]@row

Thanks- Kristi

Find more posts tagged with

Accepted answers

Paul Newcome

Lets try wrapping the math part in the middle of the text string in parenthesis so that it calculates before converting to a text string.

=IF(SUM([Financials 2]@row:[Financials 4]@row) - [Financials 1]@row>= 0, SUM([Financials 2]@row:[Financials 4]@row) - [Financials 1]@row, "(" + (SUM([Financials 2]@row:[Financials 4]@row) - [Financials 1]@row) + ")")

All comments

kcurry35

I was given this formula by a coworker but is not working either.

=IF(LEFT(Number2, 1) = "-", "(" + ABS(Number2) + ")", Number2)

Paul Newcome

Try this:

=IF(SUM([Financials 2]@row:[Financials 4]@row) - [Financials 1]@row>= 0, SUM([Financials 2]@row:[Financials 4]@row) - [Financials 1]@row, "(" + SUM([Financials 2]@row:[Financials 4]@row) - [Financials 1]@row + ")")

kcurry35

I am getting unparsable. It is like there is a missing parenthesis somewhere in there. Trying to figure it out

Paul Newcome

I don't see any out of place or missing parenthesis. Double check that I spelled your column names correctly.

Are you able to provide a snippet of the formula in the sheet similar to the snippet below?

kcurry35

Screen Shot 2023-03-17 at 7.05.49 AM.png

This calculation for 2023 In Year will be a negative value. The team wants it to show in parenthesis like normal accounting formats in excel here.

Screen Shot 2023-03-17 at 7.07.12 AM.png

Screen Shot 2023-03-17 at 7.09.24 AM.png

Paul Newcome

What about the formula you said was giving you the error? That one should be working for you.

kcurry35

Here are the errors I am getting and the formula used. #INVALID OPERATION

Screen Shot 2023-03-24 at 7.37.17 AM.png

Paul Newcome

Lets try wrapping the math part in the middle of the text string in parenthesis so that it calculates before converting to a text string.

Jake Gustafson

Any way to incorporate a thousands comma in here should the numbers be that large? i.e., -$2,000 displayed as ($2,000) rather than ($2000).

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.