binary help

[HOMMER]

I hope this can be done NEED THE HELP. Counting in binary I have a excel worsheet. Cells a - p are my cells I want the ones and zeros to show up on. In cell Q I want to type a number and the correct 1 or zero will show up in the cells a - p that make that number up. For example if I type a 3 in cell Q I want a one to show up in a and b cells because this makes three in binary. I can emale an example if this will help.

 

[bandit600]

Is there a reason that it needs to be set up this way?

=Dec2Bin(Q1) or =Dec2Bin(Q1,16)

would give the whole binary equivalent in one cell.

bandit600

 

[HOMMER]

Sorry but thats not what I need. Thanks for the help...

 

[koresnordic]

I have worked this out for you and require an e-mail address to send it. Please advise

Graham

 

[combo]

Hi, Hommer,
I've added two intermediate calculations (in R1 and S1) but it can be in A1-P1 formulas.
Additionaly, if you do not need values and string output is enough, leave VALUE function.
DEC2BIN function requires Analysis ToolPack add-in to be installed.

Write formula in R1:
=DEC2BIN(QUOTIENT(Q1,256),8)

write formula in S1:
=DEC2BIN(MOD(Q1,256),8)

write formulas in A1 to H1:
=VALUE(MID($S1,8,1))
=VALUE(MID($S1,7,1))
etc., until argument is ($S1,1,1)

write formulas in I1 to P1:
=VALUE(MID($R1,8,1))
=VALUE(MID($R1,7,1))
etc., until argument is ($R1,1,1)

Notes:

 

[Nikita6003]

Hi

stick this in cell A1 & copy accross to cell P1

=MID(TEXT(DEC2BIN($Q1),"0000000000000000"),CELL("col",A1),1)

For more rows just copy whoole lot down

HTH

Cheers
Nikki

 

[Nikita6003]

oops - you want it the other way around

try this instead

=MID(TEXT(DEC2BIN($Q1),"0000000000000000"),CELL("col",$Q1)-CELL("col",A1),1)

and copy across & down as needed

Cheers
Nikki

 

[combo]

Nikki,
DEC2BIN works between -512 and 511...

Combo

 

[Nikita6003]

yes?
he DID say he wanted 3 as binary ... ;-)

The sixteen zeros format the Dec2Bin result as 16 *chars* always - which is necesary to split into the 16 cols Hommer wanted

or is that not your point?

Cheers
Nikki

 

[combo]

3 was for me an example. If you work only with DEC2BIN function, you can fill 7 last cells with 0s immediately.
I think 16 columns are not only just for fun.

Combo

 

[koresnordic]

Is HOMMER still out there? can they inform us if they want to use the dec2bin function? The spreadsheet I have will do all the numbers from 0 to 65535 (the highest with 16 digits). it is too awkward to write here how I have done it, I would need to send the spreadsheet (or my vocabulary is not good enough to explain it correctly).

Graham

 

[Nikita6003]

In that case, try this:
In cells A1 to P1 enter the digits 0, 1, ... 15
in cells Q2 .. Qwhatever enter the numbers you need to convert
In cell A2 enter this formula:

=MOD(INT($Q2/(2^A$14)),2)

Copy across & down as needed

This shld work for values up to 2^15

Cheers
& have a good weekend
Nikki

 

[koresnordic]

Ok, I will try to describe this. In A2 to P2 enter the numbers as a binary progression (1,2,4,8,16, etc). In A3, enter the formula "=IF(B1=1,B3-B2,B3)" (less the quotes) and drag this across upto cell O3. In A1, enter the formula "=IF(A3>=A2,1,0)".Drag this across to cell N1. Enter in cell P1, "=IF(Q1>=P2,1,0)". Now when you put the decimal number between 0 and 65535 in Q1 you will get the individual binary numbers in cells A1 to P1, which is what HOMMER wanted in the first place. I hope I have not made it sound to complicated, as it is quite easy really.

Graham

 

[Nikita6003]

have u spotted the deliberate mistake in my formula? should be [

=MOD(INT($Q2/(2^A$[b]1[/b])),2)

instead of

=MOD(INT($Q2/(2^A$[b]14[/b])),2)

apologies!
y'all still have a great weekend
Nikki

Oh and Hummer - all methods above will work - just a case of deciding which one best suits your purpose

 

[HOMMER]

Email address

http://www.rmboltz@bellsouth.net.

Thank you every onr for the help...