Cell Formatting Problem

**Janc** · 12-12-2011, 07:23 PM

Thanks, now Solved

I have a simple table like so:

Please Login or Register  to view this content.

where A is average, B is actual and C is variance.

In D1 I'd like to show the contents of B1 along with C1 in brackets so I've used =A1&" ("&B1&")" but this formats the result as 0.5 (-0.1).

Is there any way to format D1 to show the values as percentage and include "+" if above average and "-" if below?

**Special-K** · 12-12-2011, 07:36 PM

Try

=TEXT(A1,"00%")&" ("&TEXT(B1,"00%")&") "&TEXT(C1,"00%")

**Janc** · 12-12-2011, 07:45 PM

Thanks this works perfectly to show the numbers in the correct percentage format

Is there any way to include a "+" sign if the number is positive?

**Palmetto** · 12-12-2011, 07:52 PM

in d1 i'd like to show the contents of b1 along with c1 in brackets

=text(b1,"#%")&" ("&text(c1,"#%")&")"&if(b1>a1,"+","")

**Janc** · 12-12-2011, 08:08 PM

Thanks that doesn't quite work as is as it adds the "+" to the end - however I've juggled it a little and got it how I want it.

Thanks very much to both of you

Edit: For future reference, this is how I've entered it: =TEXT(L1, "+#%;-#0#%;#0%#")&" ("&TEXT(K1, "#%")&")"

**vlady** · 12-12-2011, 08:24 PM

Another one...

=TEXT(B1,"#%") &"("& TEXT(B1-A1,"#%")&")"

Your c is computing b1-a1 thats why you receive - 10 right, how about to include the formula there..