excel formula - Concatenate TEXT, format only parses positive numbers -

i want concatenate 3 different cells 1 make nice table presenting average , maximum value , number of observations follows:

1.234 - 3.456 ( 7 )

i found way this, not efficient still. downside there negative numbers, reason ignored.

the formula use following:

=if(r[-24]c>1;concatenate(text(r[-8]c;"#,##0;;@")&" - "&text(r[-16]c;"#,##0;;@")&" ( "&r[-24]c&" )");concatenate(text(r[-16]c;"#,##0;;@")&" ( 1 )")) 

so question be, there i'm doint wrong "#,##0;;@" or there error in concatenate?

further chris neilsen, create or delete custom number format has:

a number format can have 4 sections of code, separated semicolons. these code sections define format positive numbers, negative numbers, 0 values, , text, in order.

<positive>;<negative>;<zero>;<text>

for example, can use these code sections create following custom format:

[blue]#,##0.00_);[red](#,##0.00);0.00;"sales "@

you not requiring colours , seem not need formatting other numbers might prefer like:

'=if(r[-24]c>1,text(r[-8]c,"#.##0;-#.##0;0")&" | "&text(r[-16]c,"#.##0;-#.##0;0")&" ( "&r[-24]c&" )")  

though before adjustment required configuration (list delimiter).

since using parentheses number of observations have avoided them negatives , because hyphen might odd have changed pipe (and 'rationalised' mix of concatenate , & mentioned @macro man).