May 28 2020 09:01 PM
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
May 29 2020 08:43 AM
May 29 2020 09:25 AM
May 29 2020 10:40 AM
Spreadsheet attached with error shown on Net ID page cell B11.
Any thoughts, recommendations, assistance appreciated.
May 29 2020 12:49 PM
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
May 29 2020 03:53 PM
SolutionThanks 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
May 29 2020 11:01 PM
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
May 29 2020 03:53 PM
SolutionThanks 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