Another tough problem

[Lewiy]

Here’s a little challenge to add to PGC’s “tough problems”.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
Using only a worksheet formula, return the negative of a 4-bit binary number in binary without using any of Excel’s number base functions (DEC2BIN(), BIN2DEC(), BIN2HEX(), etc.).
<o> </o>
e.g:
9 in binary is “1001” and -9 is “0111”, so starting with “1001”, return the result “0111”.
<o> </o>
Rules:
Shortest formula wins
No VBA – only worksheet formulae
No use of number base functions

 

[Richard Schollar]

0111 looks like a 7 to me? Shouldn't you be using a sign bit at the front to indicate whether negative or positive?

 

[Lewiy]

Richard – Apologies, should have been more clear. Make the binary number negative using two’s compliment method:
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
http://en.wikipedia.org/wiki/Two's_complement

 

[fairwinds]

So, would

=11111-SUBSTITUTE(A1-1,9,1)

satisfy the request?