Ingeniero

Spreadsheet Techniques in Physics Calculations
by Carlo Segre

1

Introduction

The spreadsheet is a general purpose calculation tool and has many uses in physics. In particular, it is very good for one-time data manipulation and for developing calculations which then can be made into programs if you need to do the calculations repetitively. Modern spreadsheets also will permit you topereform data fitting and plot your results. While there are better tools for each of these specific functions than a spreadsheet, the all-in-one nature of the spreadsheet makes it a good place to start. In the following exercises, you will be taking equations and experimental data and transferring them to a spreadsheet where they can be manipulated. There are four separate applications, each of whichdemonstrates a different aspect of spreadsheet use. In each case, there will be a brief discussion of the problem in theoretical terms followed by the problem statement. You are expected to lay out a spreadsheet on your own and write a short report using the word processor of your choice. The report should consist of a brief description of how and why you set up the spreadsheet the way you did,followed by a discussion of the results you obtained from the calculation. The text contains questions you should answer in the report and which hopefully will help you think of other conclusions to be drawn from the exercises. You may use any spreadsheet for this exercise, Openoffice works well on both Linux and Windows, Gnumeric also will work on the Linux cluster. The syntax for most spreadsheets isidentical. Turn in the assignment as a single spreadsheet with 4 tabs, one for each exercise. Any plots should appear on the sheet where the data is located. Please turn in the spreadsheet in either .ods (Openoffice) or .xls (Excel) format.

2

Projectile Motion: a “what if” Spreadsheet

One of the most important uses of a spreadsheet is the “what if” calculation. In this application, you willbe performing a simple “what if” calculation to examine the parameters in projectile motion. The problem we will be studying is that of a projectile fired from a cannon with initial velocity vo at an angle θ above the horizontal. The question we want to answer is what initial angle θmax will cause the projectile to land the furthest distance from the cannon. Some of you may already know the answerto this question, and in any case this problem can easily be solved analytically by use of Calculus, nevertheless, it is a nice straightforward example of the “what if” spreadsheet technique as applied to science. We begin by solving the analytic equations which tell how far a projectile will travel for a given vo and θ. The x-axis is defined as horizontal and positive in the direction ofprojectile travel. The y-axis is positive upward. There is no component of acceleration in the x-direction, however there is an acceleration in the y-direction due to gravity: m (1) ay = −g = −9.8 . s Remember that the acceleration is negative because gravity acts downward, in the negative y-direction. The initial velocity of the projectile is vo , its components in the x- and y-directions are: vox = vocos(θ) voy = vo sin(θ) For our situation, the basic kinematic equations of motion are: x(t) = vox t (4) (2) (3)

1 y(t) = voy t + ay t2 (5) 2 The solution to the problem of how far away in the x-direction the projectile lands is as follows. Realizing that when the projectile strikes the ground, its y-coordinate must be zero, we set the equation for y(t) = 0 and solve for the time, t. Notice thatthere are two solutions possible since this is a quadratic equation in t. The first is for the time at which the projectile is fired, and the second is for the time the projectile strikes the ground. t=0 t= −2voy . ay 1 (6) (7)

We are, of course, interested in the second solution. The application of this to a spreadsheet is your job, let me give you a few hints now. With a spreadsheet, it is...