+ and - signs in &/CONCATENATE formula

[~stein~]

Hi all - first post here! (i.e. first time I can't find a solution by searching - this forum has saved my life more times than I can count...)

I'm making a dashboard intended to track a bunch of metrics at once. For one of the charts, I'm using the below formula to create the series labels:

=B5&": "&TEXT(C5;"0,000")&" KUSD ["&TEXT(F5;"0.0%")&"]"&" // Company Share: "&TEXT(G5;"0.0%")&" ["&TEXT(I5;"0.0")&"]"

I5 contains the change in market share and can be either positive or negative (or zero), and the formula results look something like this:

[TABLE="class: grid, width: 525"]
<tbody>[TR]
[TD]Segment A: 50,000 KUSD [-1.5%] // Company Share: 61.0% [2.6]
[/TD]
[/TR]
[TR]
[TD]Segment B: 9,900 KUSD [5.9%] // Company Share: 67.0% [-5.6]
[/TD]
[/TR]
[TR]
[TD]Segment C: 17,000 KUSD [-11.3%] // Company Share: 36.2% [1.4]
[/TD]
[/TR]
</tbody>[/TABLE]

My problem is that I want to include a plus sign for positive changes in market share (not only minuses when the change is negative), i.e.:

[TABLE="class: grid, width: 525"]
<tbody>[TR]
[TD]Segment A: 50,000 KUSD [-1.5%] // Company Share: 61.0% [+2.6]
[/TD]
[/TR]
[TR]
[TD]Segment B: 9,900 KUSD [5.9%] // Company Share: 67.0% [-5.6]
[/TD]
[/TR]
[TR]
[TD]Segment C: 17,000 KUSD [-11.3%] // Company Share: 36.2% [+1.4]
[/TD]
[/TR]
</tbody>[/TABLE]

Any advice on this is much appreciated!

Cheers,
~stein~

 

[Andrew Poulsom]

You can do that with:

TEXT(F5;"+0.0%;-0.0%")

 

[~stein~]

Brilliant! Thanks a lot!!

/~stein~

 

[pjmorris]

Andrew,

Can I ask where that aspect of the TEXT FormatString is documented. It certainly doesn't seem to be part of the wonderous Bing help system, though it seems to be logical and simply an application of custom formating applied to individual cells.

Regards

 

[Andrew Poulsom]

Andrew,

Can I ask where that aspect of the TEXT FormatString is documented. It certainly doesn't seem to be part of the wonderous Bing help system, though it seems to be logical and simply an application of custom formating applied to individual cells.

Regards

The second argument for the TEXT function is the number format, like you would enter in the Number tab of the Format Cells dialog. It's quite well documented in Help for the function in Excel 2010.

 

[pjmorris]

Hi Andrew, it doesnt mention that its the same as the Format Cells dialog nor that it can have multiple parts. Thank heaven for this forum.

Have a good weekend.