Displaying -1,0 or 1 depending on cell value?

[Simon Lloyd]

Hi all,

Im trying to get a cell to display either 1, 0 or -1 depending on other cells product here's what i'm using =IF(E5=0,0,IF(OR(B$5-E5<0,B$5-E5>4),0,VLOOKUP(B$5-E5,{-1,1;-2,1;-3,1;1,-1;2,-1;3,-1},2))) i can get itto display 0 or -1 or 0 and 1, my criteria is this if E5 > B5 by any amount then display -1 if E5 = B5 then display 0 and if E5<B5 by any amount display 1.

I cant seem to manage it with the formula above, any ideas?

Regards,
Simon

[Tom Ogilvy]

=sign(B5-E5)

--
Regards,
Tom Ogilvy

"Simon Lloyd" <[email protected]>
wrote in message
news:[email protected]...
>
> Hi all,
>
> Im trying to get a cell to display either 1, 0 or -1 depending on other
> cells product here's what i'm using
>
=IF(E5=0,0,IF(OR(B$5-E5<0,B$5-E5>4),0,VLOOKUP(B$5-E5,{-1,1;-2,1;-3,1;1,-1;2,
-1;3,-1},2)))
> i can get itto display 0 or -1 or 0 and 1, my criteria is this if E5 >
> B5 by any amount then display -1 if E5 = B5 then display 0 and if E5<B5
> by any amount display 1.
>
> I cant seem to manage it with the formula above, any ideas?
>
> Regards,
> Simon
>
>
> --
> Simon Lloyd
> ------------------------------------------------------------------------
> Simon Lloyd's Profile:
http://www.excelforum.com/member.php...fo&userid=6708
> View this thread: http://www.excelforum.com/showthread...hreadid=546345
>

[JE McGimpsey]

One way:

=SIGN(B5-E5)

In article <[email protected]>,
Simon Lloyd <[email protected]>
wrote:

> my criteria is this if E5 > B5 by any amount then display -1 if E5 =
> B5 then display 0 and if E5<B5 by any amount display 1.
>
> I cant seem to manage it with the formula above, any ideas?
>
> Regards,
> Simon

[Simon Lloyd]

JE & Tom, Thanks!!!! i can't believe it was that simple, i was looking at a very long winded complicated way.....you know...sledge hammer to crack a nut!

Cheers!
Simon