Creating a Surface Area Calculator in Google Sheets

In my post about the benefits and the possibilities of using spreadsheets in middle school math classes, here are the instructions that I used to get my students started on spreadsheets. If you want it to download the PDF then click this link HERE!

You can find it electronically by following this link: https://pcauley.clarify-it.com/d/kvdxee


Creating a Surface Area Calculator in Google Sheets

This quick guide will show you how to program your very own surface area calculator in Google Sheets (or any spreadsheet program).

d355585f-60b9-447e-87d1-53c7571b4970.png

Step 1 – Create a new Google Sheet

Head over to drive.google.com and log in (if you’re note already).

Then click on New and a drop down menu will appear.

Now click on Google Sheets.

Step 1 - Create a new Google Sheet

Step 2 – Set up your spreadsheet

Now it is time to set up your spreadsheet.

Notice that each cell has a coordinate or cell reference. The columns are labeled by letters and the rows are numbered.

In these cells put this information:

  • A1 = Length
  • B1 = Width
  • C1 = Height
  • D1 = Type 1
  • E1 = Type 2
  • F1 = Type 3
  • G1 = Surface Area
Step 2 - Set up your spreadsheet

Step 3 – Input your dimensions

Now that we have the spreadsheet set up it is time to start entering in your data.

Put in the length, width and height for each 3-D object.

You can find the dimensions we used by clicking this link HERE!

 

Step 3 - Input your dimensions

Step 4 – Calculate the different types

We know there are three types of sides in a rectangular prism, so we need to find those types. However, if you just type the equation into a cell, it will not work. You have to enter it a special way in order for it to work. Check out the special characters below.

  • You must start each equation with an equal sign =
  • Addition uses the plus sign +
  • Multiplication uses an asterisk *

We can add numbers directly into the cell, but we can also tell the spreadsheet to add cells which will make it much faster for us.

So our first equation in cell D1 we will multiply the information in A2 to the information in B2 and then multiply it by two. The equation will look like the image below.

Now make your own equations for Type 2 and Type 3.

*REMINDER – ALWAYS USE AN EQUAL SIGN FIRST BEFORE TYPING IN THE EQUATION*

Step 4 - Calculate the different types

Step 5 – Replicate the equation

You do not have to type in the equation over and over again. There is a way that you can replicate the equation over and over by just clicking and dragging.

  • Now select the cell with the equation in it.
  • You will notice a small square in the bottom right hand corner of the cell
  • Click and drag that little square down.

Now do this for the Type 2 and Type 3 columns.

Step 5 - Replicate the equation

Step 6 – Calculating Surface Area

Now that we have calculated the different types, it is time to calculate the surface area.

Make sure you are in cell G2.

Now we need to add the data in cells D2, E2 and F2.

Your equation should look like the image below.

Step 6 - Calculating Surface Area

Step 7 – Replicate the Surface Area formula

Now replicate that equation like you did for Type 1, Type 2 and Type 3.

Congratulations!

You should have a working spreadsheet that will automatically calculate the surface area for you correctly everytime.

Remember you can do this with any spreadsheet program like Excel, LibreOffice or Numbers (for Mac).

 

Programming with spreadsheets – START NOW!

I’ve heard a lot of math teachers comment (to me personally) how difficult it is to integrate math into their classes. Most of the time these teachers just use apps on iPads or use websites like IXL, Mathletics or more to have students practice skills and then track the students progress. That’s fine and those apps do provide a much needed service especially in the data rich educational classroom of today.

But there is another way, an older way, that can directly tie your lessons into technology. I’m talking about using spreadsheets. Now let me be clear, you can do this with Excel, Numbers (Mac only), OpenOffice, NeoOffice (Mac only), LibreOffice, Google Sheets or Zoho Docs. Yep, that’s a lot of different programs, but the process should be nearly identical regardless. You will be teaching your students to use a spreadsheet to solve math problems. As long as you have access to any of these programs you can do this.

Right now, my sixth grade class is working on volume and surface area of rectangular prisms. Volume is pretty easy, you just multiply the length, width and height. The surface is a bit more complicated as you need to find the area of each side and then add those values up. It’s not hard to say or even wrap your head around but doing the math by hand can often lead to simple mistakes that wreck the whole problem.

I can hear people now “They need to understand what they’re doing!” Yes, yes, yes we’ve practiced surface area ad nauseam and I feel pretty confident they can calculate it, so we’re past that. Now onto the fun stuff – setting up a spreadsheet to do the work for them.

This was their first time really working with a spreadsheet so I expected a lot of questions and confusion. I had to explain the following concepts:
Cell reference
– How to enter equations using the equal sign
– The asterisks (*) as the multiplication symbol

This confusion will pass due to the repetition of the equations that you must enter. The real magic happens when you start to duplicate the formulas and it starts to solve them automatically. The reaction of my students was awesome!

You can do this with just about any equation and I would even try it with students as young as fourth grade. Once a student understands an equation thoroughly enough, they can enter it into a spreadsheet and let it do the heavy lifting.

This is a great start for students because there is so much more they can do such as make their own gradebook, customize their spreadsheets using conditional formatting, need help with more math equations, then you can empower them spreadsheets!

I’ll put my instructions in another post and include a download link for the PDF.

Patrick Cauley – @itbabble
http://www.thetechjonsey.com