Forum Discussion

Tapster's avatar
Tapster
Copper Contributor
May 29, 2020
Solved

Decimal to Binary

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 ...
Need Help
Tutorial
  • Tapster's avatar
    Tapster
    May 29, 2020

    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

Tapster's avatar
Tapster
Copper Contributor
May 29, 2020
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.
Tapster's avatar
Tapster
Copper Contributor
May 29, 2020

Tapster 

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

Any thoughts, recommendations, assistance appreciated.

  • Jos_Woolley's avatar
    Jos_Woolley
    Iron Contributor
    May 29, 2020

    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

    • Tapster's avatar
      Tapster
      Copper Contributor
      May 29, 2020

      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

      • Jos_Woolley's avatar
        Jos_Woolley
        Iron Contributor
        May 30, 2020

        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

Resources