Tag Archives: spreadsheet

Standards Based Grading(SBG) and The SUMPRODUCT Command

I could be very late to the party finding this out. If so, excuse my excitement.

I gave a multiple choice test for my IB Physics course last week. Since I am using standards based grading (SBG), I wanted a quick way to see how students did on each standard. I made a manually coded spreadsheet eight years or so ago to do this. It involved multiple columns comparing answers, multiple logical expressions, and then a final column that could be tallied for one standard. Multiply that by the total number of standards...you get the drill.

I was about to start piecing together an updated one together using that same exhausting methodology when I asked myself that same question that always gets me going: is there a better way?

Of course there is. There pretty much always is, folks.

For those of you that don't know, the SUMPRODUCT command in Excel does exactly what I was looking for here. It allows you to add together quantities in one range that match a set of criteria in another. Check out the example below:

Screen Shot 2014-10-14 at 3.28.09 PM

The column labeled 'Response Code' contains the formula '=1*(B6=E6)', which tests to see if the answer is correct. I wanted to add together the cells in F6 to F25 that were correct (Response Code = 1) and had the same standard as the cell in H6. The command in cell I6 is '=SUMPRODUCT((F6:F25)*(E6:E25=H6))'. This command is equivalent to the sum F6*(E6=H6) + F7*(E7=H6)+F8*(E8=H6)+...and so on.

If I had known about this before, I would've been doing this in some way for all of my classes in some way since moving to standards based grading. I've evaluated students for SBG after unit exams in the past by looking at a student's paper, and then one-by-one looking at questions related to each standard and evaluating them. The problem has been in communicating my rationale to students.

This doesn't solve the problem for the really great problems that are combinations of different standards, but for students that need a bit more to go on, I think this is a nice tool that (now) doesn't require much clerical work on my part. I gave a print out of this page (with column F hidden) to each student today.

Here is a sample spreadsheet with the formulas all built in so you can see how it works. Let me know what you think.
Exam Results Calculator

Proofs in Geometry - The Modification Continues...

Two statements of interest to me:

  • I get more consistent daily hits on my blog for teaching geometry proofs than anything else. Shiver.
  • Dan Meyer's recent post on proofs in Geometry gets to the heart of what bothers me about teaching proofs at all. Double shiver.

These statements have made me think about my approach in doing proofs with students in my 9th grade course, which has previously been a geometry course, but is morphing into something slightly different in anticipation of our move to the IB program. I like the concept of teaching proofs because I force students to confront the idea that there's a difference between things they know must be true, might be true, and will never be true. I started the unit asking the class the following questions:

  • Will the sun rise tomorrow?
  • Will student A always be older than her younger sister?
  • Will the boys volleyball team win the tournament this weekend>

The clear difference between these questions was also clear to my students. The word 'obviously' came up at least once, as expected.

The idea of proving something that is obvious is certainly an exercise of questionable purpose, mostly because it confines student thinking in the mould of classroom mathematics. As geometry teachers, we do this as a scaffold to help students learn to write proofs of concepts that are not so obvious. The downside is the inherent lack of perplexity in this process, as Dan points out in his post. The rules of math that students routinely apply to solve textbook or routine problems already fit in this 'obvious' category either from tradition ('I've done this since, like, forever') or from obedience ('My teacher/textbook says this is true, and that's good enough for me.')

I usually go to Geogebra to have students discover certain properties to be true, or give a quick numerical example showing why two angles supplementary to the same angle are congruent. They get this, but have a sense of detachment when I then ask them to prove it using the properties we reviewed in previous lessons. It seems to be very much related to what Kate Nowak pointed out in her comment to Dan's post. Geometry software or numerical examples show something to be so obvious that proof isn't necessary, so why circle back to then use the rules of mathematics to prove it to be true?

I had an idea this afternoon that I plan to try tomorrow to close this gap.
I wrote earlier about using spreadsheets with students to take some of the abstraction out of translating algebraic expressions. Making calculations with variables in the way a spreadsheet does shows very clearly the concept of variables, and also doing arithmetic with them. My idea here is to use a spreadsheet this way:

Screen Shot 2013-11-10 at 5.35.43 PM

Screen Shot 2013-11-10 at 5.37.39 PM

My students know that they should be able to change what is in the black cells, and enter formulas in the red cells so that they change based on what is in the black cells only. In doing this, they will be using their algebraic rules and geometric definitions to complete a formula. This hits the concrete examples I mentioned above - a 25 degree angle complementary to an angle will always be congruent to a 25 degree angle complementary to that same angle. It also uses the properties (definition of a complementary angle, subtraction property of equality, definition of congruence) to suggest the relationship between those angles using the language and structure of proof, which comes next in class.

Here is the spreadsheet file I've put together:
02 - SPR - Congruent Angles

I plan to have them complete the empty cells in this spreadsheet and then move on to filling in some reasons for steps of more formal proofs of these theorems afterwards, as I have done previously. I'd like to think that doing this will make it a little more clear how the observations students have relate to the properties they then use to prove the theorems.

I'd love you to hack away at my idea with feedback in the comments.

A computational approach to modeling projectile motion, continued.

Here is the activity I am looking at for tomorrow in Physics. The focus is on applying the ideas of projectile motion (constant velocity model in x, constant acceleration model in y) to a numerical model, and using that model to answer a question. In my last post, I detailed how I showed my students how to use a Geogebra model to solve projectile motion.

Let me know what I'm missing, or if something offends you.


A student is at one end of a basketball court. He wants to throw a basketball into the hoop at the opposite end.

  • What information do you need to model this situation using the Geogebra model? Write down [______] = on your paper for any values you need to know to solve it using the model, and Mr. Weinberg will give you any information he has.
  • Find a possible model in Geogebra that works for solving this problem.
  • At what minimum speed he could throw the ball in order to get the ball into the hoop?

We are going to start the process today of constructing our model for projectile motion in the absence of air resistance. We discussed the following in the last class:

  • Velocity is constant in the horizontal direction. (Constant velocity model)
  • x(t) = x_{0} + v t

  • Acceleration is constant in the vertical direction (Constant acceleration model)
  • v(t) = v_{0} + a t
    x(t)=x_{0}+v t +frac{1}{2}a t^2

  • The magnitude of the acceleration is the acceleration due to gravity. The direction is downwards.

Consider the following situation of a ball rolling off of a 10.0 meter high platform. We are neglecting air resistance in order for our models to work.
Screen Shot 2013-02-25 at 6.15.15 PM

Some questions:

  • At what point will the ball's movement follow the models we described above?
  • Let's set x=0 and y = 0 at the point at the bottom of the platform. What will be the y coordinate of the ball when the ball hits the ground? What are the components of velocity at the moment the ball becomes a projectile?
  • How long do you think it will take for the ball to hit the ground? Make a guess that is too high, and a guess that is too low. Use units in your answer.
  • How far do you think the ball will travel horizontally before it hits the ground? Again, make high and low guesses.

Let's model this information in a spreadsheet. The table of values is nothing more than repeated calculations of the algebraic models from the previous page. You will construct this yourself in a bit. NBD.
Screen Shot 2013-02-25 at 6.39.23 PM

  • Estimate the time when the ball hits the ground. What information from the table did you use?
  • Find the maximum horizontal distance the ball travels before hitting the ground.

Here are the four sets of position/velocity graphs for the above situation. I'll let you figure out which is which. Confirm your answer from above using the graphs. Let me know if any of your numbers change after looking at the graphs.

Screen Shot 2013-02-25 at 6.42.35 PM

Now I want you to recreate my template. Work to follow the guidelines for description and labels as I have in mine. All the tables should use the information in the top rows of the table to make all calculations.

Once your table is generating the values above, use your table to find the maximum height, the total time in the air, and the distance in the x-direction for a soccer ball kicked from the ground at 30° above the horizontal.

I'll be circulating to help you get there, but I'm not giving you my spreadsheet. You can piece this together using what you know.


Next steps (not for this lesson):

  • The table of values really isn't necessary - it's more for us to get our bearings. A single cell can hold the algebraic model and calculate position/velocity from a single value for time. Goal seek is our friend for getting better solutions here.
  • With goal seek, we are really solving an equation. We can see how the equation comes from the model itself when we ask for one value under different conditions. The usefulness of the equation is that we CAN get a more exact solution and perhaps have a more general solution, but this last part is a hazy one. So far, our computer solution works for many cases.

My point is motivating the algebra as a more efficient way to solve certain kinds of problems, but not all of them. I think there needs to be more on the 'demand' side of choosing an algebraic approach. Tradition is not a satisfying reason to choose one, though there are many - providing a need for algebra, and then feeding that need seems more natural than starting from algebra for a more arbitrary reason.