Skip to main content
Skip to main content

Creating my own spreadsheet

Integrating Digital Technologies
Years 5-6; 7-8

DT+ Mathematics

A spreadsheet can be used to do calculations quickly using formulas. How can we make a spreadsheet that converts a binary number to a decimal number? This lesson provides some guidance and Excel files for student and teacher use.

Binary number calculator

Suggested steps

Prior knowledge

To undertake this activity there is an expectation that students have an understanding of binary numbers and how to count in binary. Refer to the introduction to binary lesson.

A quick revision

Revise what students know about counting in binary, if possible use binary cards or write a decimal number on the board eg ‘21’ to model how to represent that number in binary, which is 10101. Use the following table with headings to show the progression of the binary numeral system much like 1s, 10, 100, 1000 for decimal system. Binary is a doubling pattern of 1, 2, 4, 8, 16 etc.

Use the table to ensure all students can count in binary and represent decimal numbers in binary.

Note remember to start from the left when using the table to make a decimal number. For example to make the number 31 do I need a 16, YES. Do I need and 8, NO. Do I need a 4, YES. Do I need a 2, NO. Do I need a 1, YES. So the binary number is 10101. Repeat this process for other numbers. Try making numbers 1-31. Ask what the largest number than can be made in this table.

Numbers larger than 31. If we add another column how can we make the number 43?

How can we make the number 251? Discuss the pattern of doubling to get 64 and 128 and add these two new columns.

Creating a spreadsheet

Differentiate the task depending on your student’s familiarity and skills using a spreadsheet. Scaffold the learning by providing a spread sheeting file which has the set up partly completed. The files provided are MS Excel.

Some students who are well skilled in using a spreadsheet can design their own converter and may not need a file to scaffold their learning.

Provide this file for students that have a basic understanding of how to use a spreadsheet. As a starting point ask students to test the sheet to see how it operates. Can they work out how the ASCII decimal number is calculated? Ask students if they can make their converter work up to the decimal number of 255. Encourage students to test and check to see if auto sum is correct and outputs in the cell as the correct ASCII Decimal Number.

For students who want to add a conditional if statement to automatically represent o or 1 as on or off, use this file. In this example the conditional statement reads if cell (above) = 1 then show On, else if cell = 0 show Off.

A completed version might look similar to this, see the file here.

For students that are interested in creating an interface refer to this example.

Refer to this version of the completed spreadsheet with tips that explain how the sheet is set up.

This version includes binary cards with dots, which can be used as a further challenge.

Why is this relevant?

Computers today use the binary system to represent information. It is called binary because only two different digits are used. It is also known as base two (normally we use base 10). Each zero or one is called a bit (binary digit). A bit is usually represented in a computer’s main memory by a transistor that is switched on or off, or a capacitor that is charged or discharged.

One bit on its own can’t represent much, so they are usually grouped together in groups of eight, which can represent numbers from 0 to 255. A group of eight bits is called a byte.