<--- Return to Excel Statistics Guide

An Application of Constructivist Learning using Microsoft Excel

Velocity of Falling Objects

By Keith Greiner

September 3, 2018

To learn something well, a person needs to study, interact, create, and construct. While some in education may debate it, I find that the acts of creation and construction are among the most effective ways to learn something. If I create an object and carefully construct it, I know what considerations went into the creation and I know what pieces went into the construction. Learning, in that context is directly proportional to the amount of effort required to sort out the issues of creation and construction. When it comes to mathematics, I find that understanding is improved if I put the mathematics to work by creating different ways of viewing and idea (often represented by a formula), and by performing a variety of calculations. This is called “constructivist” learning. It can be used to take a difficult subject (mathematics) and make it substantially easier to understand. The understanding improved with the addition of a depth and breadth that is not available in a passive reading of the formulae.

For those who might want to follow along, the following example activity is to build a spreadsheet that uses three formulae related to acceleration of falling objects. I recall from my high school studies, many years ago, that the formulae were learned through rote memorization, with effort focused on recognizing the formulae during a test while performing only a few basic calculations. By constructing an Excel spreadsheet, it is possible to experiment with the formulae and discover much more about how numbers behave in the formula’s context. The experience leads me to suggest that if high school and college faculty, as well as textbook publishers are to meet the needs of learners, then they should incorporate many episodes of spreadsheet-based constructivist learning like the one described below. I would like to see examples like this in every class that uses mathematics and in every textbook.

In this example, I’ll use three equations that relate to high school physics

1. Velocity of a free falling object that has traveled a certain distance,

2. The time required to fall a certain distance, and

3. The distance traveled, given a certain amount of time, (a confirmation calculation for item 2.

…where “v” is the velocity, “g” is the acceleration due to gravity, and “d” is distance. Numbers may be in any system of units, so long as they remain within the constraints of the system. Here, we use the system of meters, kilograms, and seconds, collectively described as “metric” as and the system of feet, pounds, and seconds, collectively described as FPS.

Now, lets construct the spreadsheet. For comparison purposes, we’ll build it in both the metric system and the FPS system. In Excel, we can set up a sheet in which two systems of measurement can interact and verify each other, and two of the three formulae can be self-proving. We’ll use the same formulae for metric and FPS. We will also verify that the metric value for the acceleration of gravity is the same as the FPS value: just a different measurement system.

The first step is to translate the formulae into Excel Code. The spreadsheet image is designed to provide information for users to reconstruct the calculations and the reader is encouraged to build her/his own spreadsheet based on this example. Start by constructing a sheet that is exactly as shown, and then use your creative skills to try out new ideas based on the original.

Begin by noting the first row and the first column marked in yellow. I use this convention as a substitute for the grey borders provided by Excel because the yellow borders can be copied and pasted to a Word document and to an html page. The grey borders provided by Excel can only be included if the sheet is saved as an image. I decided to show the spreadsheet in two parts. The first part shows the calculations in metric units in rows 8 through 16 and then in FPS units in rows 21 through 28. Rows 30 through 35 show check values. Results of the check value calculations may be used to verify whether the other calculations are correct.

On the right, in column I, you may find the Excel Code for column F. If you set up the spreadsheet exactly as shown, you can enter the code as shown and obtain the results that are shown.

Notice that the test values in rows 30 through 35, all use the Excel =CONVERT(number , from_unit , to_unit ) function to convert metric values to FPS values. If the programs work, all those will match the corresponding values of the FPS section.

While it would be possible to simply post the completed spreadsheet, the user is encouraged to follow this route. The process of constructing the spreadsheet is the central part of learning.

<--- Return to Excel Statistics Guide