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:

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).


Step 1 – Create a new Google Sheet

Head over to 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.


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.


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

Beyond Hour of Code

By: Tony DePrato | Follow me on Twitter @tdeprato

A friend emailed me to get some ideas for his IB Computer Science (IBCS) class. We had recently discussed The Hour of Code, and how it was a good activity, but it was not deep enough to accomplish anything real.

I gave my friend the same advice I always remind myself to take. If you want to meet your curriculum standards and be interesting, teach students how to make their own math.

The example I gave to my friend went like this, and I typed it out in about five minutes, so forgive the loose structure:

  • Have students write a simple grade calculator. The calculator needs to tell the student what score or combination of scores need to be earned in order to reach a target grade. For example, I have a an 89% and I want an ‘A’. What grade do I need to get next or over the next few assessments?
  • Then, introduce this question, “Are all courses, grades, and the effort put into activities rewarded the same?”. The goal is to get the students to start looking at the work they do at school in degrees of importance, chance and statistics, etc. For example, how much effort should go into earning an ‘A’ for an elective vs earning an ‘A’ for a core IB course? In fact, does my class grade even matter that much compared to my overall IB score?
  • Students then revise their work adding more variables and creating metrics to measure aspects of their academic life. They are creating new metrics. They are learning to assign value to things, in a way that is meaningful to them.
  • This activity is automatically differentiated. Standard level students in standard level math courses will use different approaches than higher level students. Students who are strong in economics or business, but weak in math, will approach the problem from completely different level as well.
  • The assessment is not that difficult. There are always standard IBCS specific benchmarks that need to be considered. They can form 30%-50% of the assessment plan. The remaining percentage could involve creativity, real-world application, ability to reproduce results, etc.
  • Students can do this individually at first, and then in teams to refine their solutions. This is an excellent way to simulate the type of collaboration that happens when people decide to make something new. The team will have to compromise, probably choose a leader, etc. Some will fail. Few will actually succeed. Who said IBCS class cannot be the starting point for entrepreneurship?

Encouraging the use of tools and problem solving is great, but unless students can see beyond language constructs, they are not going to ever get away from programming for the sake of programming. Programming is a tool. Excellent programmers are creative and inventive. Unless those last two concepts are nurtured through planning students will never realize their potential. Also, this keeps IBCS from being boring.

If you want some ideas for programming projects for grades 3-12, send me an e-mail:

Not the Best, Not the Worst, and Getting the Job Done,fl_progressive,q_80,w_636/18l7vwlkkqp45jpg.jpg

I was reading an article on Slashdot, by far my favorite website. The Slashdot posting linked to this original article, The programming talent myth. The article discusses this perception that programmers are either rock-star-ninjas or barely able to string to strings together (that was a programming joke by the way).

However, the author, who is very accomplished as a programmer and technology professional says something very compelling, and something very applicable to the whole of education,

If the only options are to be amazing or terrible, it leads people to believe they must be passionate about their career, that they must think about programming every waking moment of their life. If they take their eye off the ball even for a minute, they will slide right from amazing to terrible again leading people to be working crazy hours at work, to be constantly studying programming topics on their own time, and so on.

The truth is that programming isn’t a passion or a talent, says Edge, it is just a bunch of skills that can be learned. Programming isn’t even one thing, though people talk about it as if it were; it requires all sorts of skills and coding is just a small part of that.


I felt like I was a bad math student until I was almost 24 years old. I was so convinced I was bad at math, that I assumed I would be awful at programming. I would always work with technology that was based in or around some environment that aided me in development.

Then one day, as these stories go, I saw an interesting book, and randomly bought it. I literally judged the book by the cover. The book was titled Fermat’s Enigma: The Epic Quest to Solve the World’s Greatest Mathematical Problem.

I began reading it, prepared to skip the math and get to the story. However, this was impossible, as the math was the story. I learned many interesting things. First off, all these “good at math guys” were normal people with mostly boring jobs who did math as a hobby. Second, I was able to actually understand and do the math. How was that possible? How could I, someone who had always struggled with math textbooks, read and understand this book about mathematics?

The reason I could understand it, was because I could read, and this book was written for normal people, unlike a textbook which is written to help teachers plan and meet standards. All I needed was to read the information in a different way, and then have the resources required to look-up things I was confused about.

Once this small break through happened, I started programming for real, and from scratch. Whenever I would go to online forums, I would feel like a fool because everyone seemed to be a rock-star-ninja. I did not let this bother me though, I persisted. I realized I would often only have time to program a few hours a week or sometimes only a few hours a month. I was not a programmer, I was a teacher with a full-time job. These forum ninjas were probably living the life of a programmer, and working on their skills full-time.

As time went on I wrote programs for operating systems, websites, DVDs, etc. I eventually started teaching programming, and often would question if I was doing the right thing being a teacher, while not being a rock-star-ninja. I found that when I have very talented students, they could easily learn programming faster than I could, so I would help them learn things like project management, documentation processes, and how to speak to people normally. I reminded them that in the real-world they would have clients, and those people would not want to deal with someone wearing all black and missing three days worth of showers.

For the majority of students, who were simply average at programming, I told them my story. I showed them things I had done in the past, and made it clear that they were in fact able to do more than I had done because they were starting younger. I expressed to them that having a good idea would drive their work and help them find people to assit them when needed.

This journey continues.  Programming turned into competitive robotics, and now in 2015, drones and 3D printing are the new challenges.

The key theme with the article that inspired me to write this, and my personal experience, is alienation. I was alienated, or isolated, from mathematics. I was separated at an early age by perception, from groups of people who were considered competent. This happened to me before high school. I believed firmly by year 7 that I was a bad math student. By all local measurements, I was a bad math student.

As I witness schools pushing to increase programming competency and standardized test scores in math, I begin to worry. I do not think any broad curricula, such as AP and IB, are as holistic as my programming curriculum. I think their learning objectives are driven by quantifiable outcomes, just like standardised math testing.

How can we measure all the pieces required to actually make something useful with measurement tools designed to evaluate a single answer? When do we start teaching students all the other skills they need to create, regardless of whether or not that creation is in code or in some other medium? Those skills being and not limited to, project management, planning, design,team work, testing, budgeting, etc.

If you did not know, the guy at the top is MacGyver. MacGyver’s character was always presented as a logical jack-of-all-trades who could find solutions to unpredictable scenarios. I would rather have more MacGyvers than rock-star-ninja’s, because MacGyver can adapt and learn and find new solutions to a larger variety of problems. MacGyver can be a programmer when needed, logistician, a statistician, a salesperson, an entrepreneur,etc.  I have a feeling MacGyver was a B-C student, who cared more about the why than the how. MacGyvers are going to understand the core and not just follow the common core.

Tony DePrato

iPhone/iPad app of the week – MyScript Calculator


This app is truly awesome. It is an app that can truly be used in early elementary classes and be used all the way up through college level mathematics. What it allows you to do is literally write (using your finger or stylus) the equation and it will solve it for you. Certainly on the iPhone and it’s smaller screen it is not as effective as say and iPad mini or a full size iPad. It simply works quite well. There is no help on how to do the equation you’re working on, so you need to know what you’re doing (especially for that upper level math). Best of all this bad boy is free. Check it out here.

wpid-img_1940-2013-01-13-19-31.png wpid-img_1941-2013-01-13-19-31.png

wpid-img_1944-2013-01-13-19-31.png wpid-img_1945-2013-01-13-19-31.png

A fun trick using simple math

I’ve never heard of Richard Wiseman before this, but apparently he is a very well known and respected psychologists from the UK. The video is a fun way to use simple math to “predict” what square you end up on. Can you or your students figure it out? Even if you can it’s still a fun little trick that will get your students thinking and that’s not a bad thing.