1. Limited time only! Sign up for a free 30min personal tutor trial with Chegg Tutors
    Dismiss Notice
Dismiss Notice
Join Physics Forums Today!
The friendliest, high quality science and math community on the planet! Everyone who loves science is here!

EXCEL - Binary to Decimal Worksheet

  1. Oct 19, 2011 #1
    Ok, this one exercise is really getting to me. Either I missed something, or our prof hasn't gone over any of this. I was hoping someone could decode this for me.

    Question:

    ____________________________________________________________________________



    [PLAIN]http://img829.imageshack.us/img829/996/screenshot20111019at618.png [Broken]


    The Base cell is used to specify the base of the number that will be Input. When values are entered in the cells the sheet converts from the base specified into Decimal. To do this you will need several formulae.

    Normal form is required because Excel will ignore any leading zeroes in the Input. To guarantee that the number has 8 digits it will be necessary to convert it to a string and pad the left end with 0’s as necessary. This can all be accomplished with the TEXT function.
    TEXT (value, format_text)
    value is a numeric value, a formula that evaluates to a numeric value, or a reference to a cell containing a numeric value.
    format_text is a number format in text form from the Category box on the Number tab in the Format Cells dialog box.
    For our purposes we can specify exactly 8 digits using the format specification: “00000000”. The formula for Normal form then is:
    = TEXT (Input, “00000000”)
    Now that the data is in Normal_form we can create a single formula to extract each digit and place it in the appropriate cell in the range named bit. This formula requires some thought. How can Excel copy characters from a string into different cells? The key to the answer is in the fact that the digits are characters. We can therefore use a TEXT function. The formula will be located in cells C7 through J7. In C7 it needs to select the first character from Normal_form, while in D7 it needs to select the second character, etc. The MID function looks like the best choice.


    We know the name of the string and the number of characters needed so the formula will resemble this one:
    =MID (Normal_form, start_position, 1)

    Of course, the calculation of start_position is still the key.

    You might be tempted to simply replace start_position with an integer: 1 in cell C7, 2 in D7, etc. But this is clearly NOT the best way to handle the task. Hard coding literal values in formulae is generally a bad idea. What’s needed is an expression that will generate the appropriate sequence of digits, namely 1 in C7, 2 in D7, etc.

    As it turns out, we have a descending sequence of integers just above in the Exponent row. Subtracting a descending sequence from the length of the string will produce the numbers we need. This part can be accomplished with
    LEN (Normal_form) – Exponent
    So substitute this expression for start_position to complete the formula.

    You will also need to write a formula to calculate the cells in the value range. These values are simply the Base raised to the Exponents.

    With the value and bit ranges defined you can multiply them together to get the extension values and SUM these to get Decimal.

    ____________________________________________________________________________


    Any help is appreciated.
     
    Last edited by a moderator: May 5, 2017
  2. jcsd
  3. Oct 19, 2011 #2

    berkeman

    User Avatar

    Staff: Mentor

    We don't do your schoolwork for you here on the PF. What are your thoughts on how to proceed? Have you done the first couple of conversion formulas? What do they look like?
     
    Last edited by a moderator: May 5, 2017
Know someone interested in this topic? Share this thread via Reddit, Google+, Twitter, or Facebook




Similar Discussions: EXCEL - Binary to Decimal Worksheet
  1. Excel question (Replies: 1)

  2. Excel Help (Replies: 3)

Loading...