EXCEL - Binary to Decimal Worksheet

[axellerate]

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


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.

 

[berkeman]

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


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.

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?