SOLVED

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

Copper Contributor

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

@Wr1nkly 

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.

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.
AH! "$0.00"

Thanks again for the response, very helpful

@Wr1nkly 

Even better, to display thousands separators too:

 

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

@HansVogelaar  

 

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.

 

@Wr1nkly 

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

@Wr1nkly 

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.

View solution in original post