Need help w/formula

I need a fx to do 2 things (AND…)

I have 2 columns (below) that need to calculate: Annual Cost (Current) - Annual Cost (Proposed) AND I need the $ amount (if negative $$$) to show as parenthesis instead of a -

column 1: Annual Cost (Current)

column 2: Annual Cost (Proposed)

My current SUM is below:

=SUM([1. Annual Cost (EA) (Current)]@row - [1 Annual Cost (EA) (Proposed)]@row )

How to I include the AND statement to create the condition that anything less than $0 to show as ($1500.00) instead of -$1500.00

@Paul Newcome 😀

Find more posts tagged with

Accepted answers

All comments

Paul Newcome

@Not so formula savvy This can actually be a bit of a challenge depending on your exact needs. Also keep in mind that the output from this formula will be text values. You will still want to keep the number values from the [ColumnA]@row - [ColumnB]@row in a column somewhere if you are planning to further evaluate them for metrics.

It looks like you don't need commas though which greatly simplifies things. If that is the case, you can use this:

=IF([1. Annual Cost (EA) (Current)]@row - [1 Annual Cost (EA) (Proposed)]@row < 0, "(", "") + "$" + ([1. Annual Cost (EA) (Current)]@row - [1 Annual Cost (EA) (Proposed)]@row) + IF([1. Annual Cost (EA) (Current)]@row - [1 Annual Cost (EA) (Proposed)]@row < 0, ")", "")

If you do need commas, let me know. I have a solution saved that I can copy/paste in for you.

Not so formula savvy

Hi Paul,

i plugged in the fx provided and it shows like this (-1500.00)….so it has both minus sign and parenthesis….which is fine. thanks for all your helps as always!!!

Not so formula savvy

The other thing that's happening with the fx is that it seems to negate any other formatting rules I have in place for e.g, I currently have formatting for any $$ less than zero to show as red….and any $$ greater than $0 to show as green…but with the fx, it just shows as black….

Paul Newcome

My apologies. I meant to put an ABS in there:

IF([1. Annual Cost (EA) (Current)]@row - [1 Annual Cost (EA) (Proposed)]@row < 0, "(", "") + "$" + ABS([1. Annual Cost (EA) (Current)]@row - [1 Annual Cost (EA) (Proposed)]@row) + IF([1. Annual Cost (EA) (Current)]@row - [1 Annual Cost (EA) (Proposed)]@row < 0, ")", "")

As for the formatting, you will need to change it to be red when it contains an open parenthesis and green when it does not. Reformatting this way converts it into a text string. This is also important to keep in mind if you plan to use this data in further metrics such as adding or averaging. This column will not be usable for these calculations. You would need to keep the regular numerical output in a separate hidden column.

Not so formula savvy

oh ok…good heads up…i will make note…thanks Paul!

Not so formula savvy

Just an observation, I also noticed the output no longer shows the "," ? so for example, instead of showing 69,000, it shows as 69000…?

Paul Newcome

Right. My first post mentioned that as well. If you need commas, this will work from -999,999,999,999.99 to 999,999,999,999.99.

=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))))) + SUBSTITUTE("." + MID(ABS([Total Column]@row - INT([Total Column]@row )) + "00", 2, 3), "..", ".") + IF([Total Column]@row < 0, ")", "")

Not so formula savvy

😲…LOL…i will keep this one in my back pocket if I get asked to fix…LOL…you're the best!!!!

Paul Newcome

As always… Happy to help. 👍️

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.