Complex Concatenation

[molsviking]

I am trying to concatenate a complex string of text and percentages and variances and struggling to get it to work. Can any geniuses out there assist me?

A1 = "Revenue"
A2 = % which could be +/- which is referencing Cell B2, ie -5.3%. Negative figure not required to be pulled through
A3 = "up/down" (based on whether A2 is negative or positive figure) "against forecast"
A4 = &
A5 = % which could be +/- which is referencing Cell B5, ie -7.2%. Negative figure not required to be pulled through
A6 = "vs last year"

So what i would like to see is:

Revenue 5.3% down against forecast & 7.2% down vs last year

Many thanks in advance!

 

[gaz_chops]

Welcome to Mr Excel,

Try

=A1&" "&TEXT(ABS(B2),"#.00%")&" "&A3&" "&A4&" "&TEXT(ABS(B5),"#.00%")&" "&A6&" "&A7

[TABLE="width: 174"]
<colgroup><col width="87" span="2" style="width: 65pt;"></colgroup><tbody>[TR]
[TD="width: 87"]Revenue[/TD]
[TD="width: 87"][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]5.30%[/TD]
[TD="class: xl63, align: right"]5.30%[/TD]
[/TR]
[TR]
[TD]Up[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]&[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]-7.20%[/TD]
[TD="class: xl63, align: right"]-7.20%[/TD]
[/TR]
[TR]
[TD]Down[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]vs last year[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Revenue 5.30% Up & 7.20% Down vs last year[/TD]
[/TR]
</tbody>[/TABLE]

 

[molsviking]

Thanks Gaz

This works in principle, however it calculates as follows:

Revenue 530.00% up and 720.00% down vs last year

What do i need to tweak to get those % figures to 5.3% & 7.2%?

The cells are currently formatted as General however I have tried Percentage and neither make a difference.

 

[gaz_chops]

Try

=A1&" "&TEXT(ABS(B2/100),"#.00%")&" "&A3&" "&A4&" "&TEXT(ABS(B5/100),"#.00%")&" "&A6&" "&A7

 

[molsviking]

Gaz, you are a superstar. It works! Many thanks for your prompt responses.

 

[gaz_chops]

You're welcome, glad we got it working.