Incorporating round function into formula

[howard]

When incorpating the round function into my formula, it returns #value !. Without the round formula, I get no error

=ROUND(IF((C2-B2)-1>0,TEXT((C2-B2)-1,"#,#0")&UNICHAR(9650),TEXT((C2-B2)-1,"#,#0")&UNICHAR(9660)),0)&""

It would be appreciated if someone could amend my Code

 

[RasGhul]

Try

=IF((C2-B2)-1>0,CONCATENATE(ROUND(VALUE(TEXT((C2-B2)-1,"#,#0")),1)&UNICHAR(9650)),CONCATENATE(ROUND(VALUE(TEXT((C2-B2)-1,"#,#0")),1)&UNICHAR(9660)))

or

=IF((C2-B2)-1>0,CONCATENATE(ROUND(VALUE(TEXT((C2-B2)-1,"#,#0")),1)&UNICHAR(9650)),CONCATENATE(ABS(ROUND(VALUE(TEXT((C2-B2)-1,"#,#0")),1))&UNICHAR(9660)))

If you don't want a negative number before your arrow.

 

[howard]

Thanks for the help. The formula now returns a circular reference


See sample data

Book1
	A	B	C
2	30	25	0
3	10	8	0
4	38	47	0
5	49	44	0
6	8	12	0
7	43	49	0
8
Sheet1

Cell Formulas
Range	Formula
C2	=IF((C2-B2)-1>0,CONCATENATE(ROUND(VALUE(TEXT((C2-B2)-1,"#,#0")),1)&UNICHAR(9650)),CONCATENATE(ABS(ROUND(VALUE(TEXT((C2-B2)-1,"#,#0")),1))&UNICHAR(9660)))

 

[RasGhul]

It will result in circular reference if your formula is in the C column as the formula compares C & B.

If your comparing A & B columns;

[COLOR=#333333]=IF([/COLOR][COLOR=Blue](A[COLOR=Red]2-B2[/COLOR])-1>0,CONCATENATE([COLOR=Red]ROUND([COLOR=Green]VALUE([COLOR=Purple]TEXT([COLOR=Teal](A[COLOR=#FF00FF]2-B2[/COLOR])-1,"#,#0"[/COLOR])[/COLOR]),1[/COLOR])&UNICHAR([COLOR=Green]9650[/COLOR])[/COLOR]),CONCATENATE([COLOR=Red]ABS([COLOR=Green]ROUND([COLOR=Purple]VALUE([COLOR=Teal]TEXT([COLOR=#FF00FF](A[COLOR=Navy]2-B2[/COLOR])-1,"#,#0"[/COLOR])[/COLOR]),1[/COLOR])[/COLOR])&UNICHAR([COLOR=Green]9660[/COLOR])[/COLOR])[/COLOR][COLOR=#333333])[/COLOR]

 

[Tetra201]

Is this what you need?

=TEXT(ROUND(C2-B2-1,0),"#,#0")&IF(C2-B2>1,UNICHAR(9650),UNICHAR(9660))

 

[howard]

Thanks for the help, much appreciated

 

[Tetra201]

You are welcome.