Summing data and applying parenthesis to negative value.

kcurry35

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

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) + ")")

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

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.

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

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) + ")")

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).