Excel : decimal to binary conversion not working

[Lemming Train]

I'm trying to convert a decimal column to binary in another column but no matter what I try I get #NUM! and I can't figure out why. I created decimal to hex conversion in column B just fine with a simple DEC2HEX(A1) but doing DEC2BIN(A1) in column C just gives me #NUM!. I've tried setting the 24 bit character length in the formula but that doesn't work either.

Example of what I have :

COL-A = 10485761 decimal
COL-B = A00001 hex (from DEC2HEX(A1) formula)
COL-C = should produce 101000000000000000000001 (formulas tried won't work!)




Edit, pasted in wrong numbers. Edit2, 3rd time lucky!

Edited by Lemming Train on Friday 16th August 16:08

[M.J.S]

https://support.office.com/en-us/article/dec2bin-f...

DEC2BIN only works up to 511, any higher and you get the #NUM error.

[Lemming Train]

M.J.S said:

https://support.office.com/en-us/article/dec2bin-f...

DEC2BIN only works up to 511, any higher and you get the #NUM error.

Thanks MJS, yes I was just doing some research and found that exact same thing! I have stumbled upon a StackOverflow post where someone had the same problem and a solution was posted which I've tested and works !

=DEC2BIN(MOD(QUOTIENT(A1,16^5),16),4)&" "&DEC2BIN(MOD(QUOTIENT(A1,16^4),16),4)&" "&DEC2BIN(MOD(QUOTIENT(A1,16^3),16),4)&" "&DEC2BIN(MOD(QUOTIENT(A1,16^2),16),4)&" "&DEC2BIN(MOD(QUOTIENT(A1,16^1),16),4)&" "&DEC2BIN(MOD(QUOTIENT(A1,16^0),16),4)

Fixed!

Edited by Lemming Train on Friday 16th August 16:40