HEX Numbers, Hex to Bin

[nickgreening]

I would like to convert a Hex number to Binary number. When i convert ff it works correctly, but when i convert E97 i get an message saying #¡NUM!
i think this is because th number E97 is seen by excel as text and not a number, but FF is okay! don't understand!!! i am using Excel 2003 SP2, thanks

 

[GlennUK]

When i convert ff it works

... tell how you are converting please.

 

[Richard Schollar]

Hi Nick

From the Excel Help~:

If number is negative, it cannot be less than FFFFFFFE00, and if number is positive, it cannot be greater than 1FF.

So the number is too big for HexToBin.

Best regards

Richard

 

[GlennUK]

Ah, Richard, well spotted.

 

[nickgreening]

thanks, guys

 

[barry houdini]

You can use this formula to convert positive HEX to binary accurately up to 7FFF

=(DEC2BIN(HEX2DEC(B2)/64)&DEC2BIN(MOD(HEX2DEC(B2),64),6))+0

where your hex number is in A1

edit:"simplified" formula

 

[Richard Schollar]

Arbitraging the formulas! Where on Earth do you get this stuff from Barry?

 

[barry houdini]

It's really just a variation on a way to extend DEC2BIN so that it can give 18 bit results

=DEC2BIN((A1/512),9)&DEC2BIN(MOD(A1,512),9)

In fact it's one of the very first formulas I posted on these boards, although, if memory serves, I pinched it from another (inferior) board.

http://www.mrexcel.com/board2/viewtopic.php?t=137980&highlight=bin2dec