SOLVED

# Showing two Values in single cell (+/- change)

Copper Contributor

# Showing two Values in single cell (+/- change)

This seems like an easy fix or maybe not but i just cant figure it out.

I am trying to show the %, Amount, or \$ change from one cell to another. While retaining the current Value.

For Example, Cell A1 would show \$100, Cell B1 would show \$150 and I would like it show \$150 (+\$50).

I am wanting to see the total value but also the +/- in the same cell. For general numbers, %, and \$ amounts.

Is this possible?

6 Replies
best response confirmed by Wr1nkly (Copper Contributor)
Solution

# Re: Showing two Values in single cell (+/- change)

You cannot do this in B1 itself.

I'd create a formula in column C. In C1:

=TEXT(B1, "\$0")& " (" & TEXT(B1-A1, "+\$0;-\$0") &")"

This can be filled down. You can use a similar format for cells with a general number or a percentage, but you'll have to create a separate formula for each of those.

# Re: Showing two Values in single cell (+/- change)

OH! this is exactly what i want, i can pull the B1 and A1 data from else. i.e. create the formula in a hidden cell and c/p it to the cell i desire! Thank you so much!

One extra thing, is there a fix for the decimal point? ill have to just show you what i mean i think

D21 : \$21,741.97 E21: \$24,469.80
you formula shows \$24470 (+\$2755)

This is exactly what i was trying to do, but id like to so the original \$24,469.80 instead of the 24,470.

# Re: Showing two Values in single cell (+/- change)

AH! "\$0.00"

Thanks again for the response, very helpful

# Re: Showing two Values in single cell (+/- change)

Even better, to display thousands separators too:

=TEXT(B1, "\$#,##0.00")& " (" & TEXT(B1-A1, "+\$#,##0.00;-\$#,##0.00") &")"

# Re: Showing two Values in single cell (+/- change)

Thank you again for all the help. My spreadsheet is coming along nicely. Im not curious, i know you can create an IF statement to change the font color. How would i add that into the previous formula you helped me with. The IF statement would be used to change the font to green/red for +/- values.

Would make it much easier to quickly see the progress and regression from period to period.

If you can help with this, that is.

# Re: Showing two Values in single cell (+/- change)

You can change the font color for the entire cell by creating conditional formatting rules.

It is not possible to color part of the result of a formula differently from the rest.

1 best response

Accepted Solutions
best response confirmed by Wr1nkly (Copper Contributor)
Solution

# Re: Showing two Values in single cell (+/- change)

You cannot do this in B1 itself.

I'd create a formula in column C. In C1:

=TEXT(B1, "\$0")& " (" & TEXT(B1-A1, "+\$0;-\$0") &")"

This can be filled down. You can use a similar format for cells with a general number or a percentage, but you'll have to create a separate formula for each of those.