Returning + or - for a number result?

[hoff123]

Hi All,

I am trying to work out a function to do the following (but not with much luck).

= A1 – B1. If the result is a positive number return +#. If the result is a negative number return -#

Seems simple enough but just got a brain freeze for a Monday morning!

Thanks!

 

[anthonya2369]

use the formula:

if(c1>0,"+#","-#")

 

[erik.van.geit]

hell, hoff123,
try this
=SIGN(A1 -B1)
this will return zero if A1 = B1
kind regards,
Erik

 

[PaddyD]

variation on the theme:

=CHOOSE(SIGN(A1)+2,"-",0,"+")

 

[erik.van.geit]

Paddyd,
NICE!
but do yo mean
=CHOOSE(SIGN(A1-B1)+2,"-",0,"+") ?

 

[PaddyD]

"but do yo mean..."

Yep - thanks for that.

 

[Norie]

Why not just use a custom format?

+#;-#

 

[erik.van.geit]

Why not just use a custom format?

+#;-#

FINE! how do you avoid the "+" when the result is ZERO ?

 

[Norie]

Well you would add the zero part to the custom format.

As the OP didn't specify what they would like for that I left it out.

+#;-#;"zero"

 

[erik.van.geit]

I was lazy, but now figured it out. Thanks for not giving the solution , allowing me to think about...
+#;-#;0
best regards,
Erik