Decimal to Binary

[Tapster]

I am trying to create a spreadsheet that takes an Decimal IP address and populates individual cells within the octets with the binary value equivalents. I need to keep a running value total based on 1's and 0's in order to calculate the next field. Everything I'm doing is circular and it's driving me nuts. For example, I'll take the first octet with the Decimal 152 for conversion.

=IF(B3/B6>1,1,0) and the cell populates a 1 (152/128>1)

I'm keeping a running total with

=SUMIF(B8:I8,"=1",B6:I6) and the field populates with 128 the first go. However, I can't use that cell as a running total because it's circular.

I'm going to bed but thought I'd see if anyone had a suggestion or tutorial recommendation I could review when I get back on this in the morning. I am aware that the =DEC2BIN(B3,0) will give me the binary value but it doesn't allow me to associate the power of two position that I am trying to stress in this lesson.

Hopefully this makes sense to someone.

Thanks in advance.

TAPS

[#2]

I got the result I wanted using the DEC2BIN and MID functions. Piece of cake once one figures out the formula to use. Running totals within the octet with SUMIF function. Now I'm trying to find the video by the Aussie I viewed last night on how to merge and center without the merge and center function.

[#3]

I spoke to soon. Cells B8:B9 are populated with the MID function. The value in both cells is 1. I run an AND on the two cells and get an error that "A value used in the formula is of the wrong data type". I was feeling good for a bit and I did find Wyn's video to answer my other question.

[#4]

@Tapster 

Spreadsheet attached with error shown on Net ID page cell B11.

Any thoughts, recommendations, assistance appreciated.

[#5]

@Tapster 

MID returns a text value, not a numeric. Either coerce your results from MID to numerics viz, for example (cell B8):

=MID(AL5,1,1)+0

else leave them as text and amend your checking formula (cell B11) to:

=AND(B8+0,B9+0)

Personally I'd prefer the former option as working with numerical 0s and 1s is both more logical and practical.

Regards

[#6 · Accepted solution]

@Jos_Woolley 

Thanks for this. It worked like a champ. Can you direct me to where I could read a bit about what the +0 is doing for me.

Have a great weekend and again, thanks.

TAPS

[#7]

@Tapster 

Any suitable mathematical operation which leaves the value unchanged will suffice to coerce to a numeric. So, as well as addition of zero, you could also use multiplication by 1, division by 1, or even the 'double unary', i.e. two minus signs (--).

There are also functions which perform this coercion if you prefer, for example VALUE.

So, if A1 contains a number stored as text, all of the following will return a numeric:

=A1+0

=A1*1

=A1/1

=--A1

=VALUE(A1)

Regards

[#8]

Thanks for this.