Spreadsheet Computation in Introductory Physics Labs

posted July 24, 2020 and revised August 12, 2020
by Jennifer Birriel & Ignacio Birriel, Morehead State University

After attending the PICUP work shop in July 2019, we were skeptical of the idea of including computation in our introductory physics courses.  The algebra-based and calculus-based based introductory courses both serve a diverse population of students and most of them are not interested in coding or computation.  Computational physics is a hard sell to your average life science major or aspiring middle school science teacher or even a pure computer science major.

Then Covid-19 forced us to move our introductory labs online in March of 2020 and we began to reconsider the place of computation in our introductory courses. Suddenly, it seemed that the introductory labs were a perfect place to implement computation using spreadsheets. We replaced our lab data sheets with an Excel “template” and students used Excel as a data processing tool. 

We quickly saw the advantages of using Excel. In most of our labs all calculations are done by hand; students repeatedly calculate the same quantity for multiple trials using a handheld calculator. What is the value of such a task? Does it help the student understand physics better? Surely, the students’ time can be better spent analyzing experimental results, examining experimental uncertainties, and learning some basic computing! 

Calculations with Excel: Computational Skills & Vocabulary

When a student performs lab calculations using a spreadsheet they learn how to develop algorithms, but that is only the beginning!  We should take the opportunity to connect spreadsheet skills to computational vocabulary. Excel has its own “syntax” and when it isn’t correct, the student receives “compiler errors” upon pressing “Enter”. Students should be encouraged to use a calculator to verify their spreadsheet output, to verify their algorithm functions correctly. They often experience run time errors when they make mistakes, e.g. division by zero or taking the square root of a negative number. Each of these yields a different “compiler” error message.  In this process, students are learning to “debug”!

When referring to cells for computation, students are using “pointers”.  Doing a “fill down” is employing an iterative loop.  When they use functions, these are called from a standard library and the student must “pass” a value to the function.  (Incidentally, they usually don’t know that trigonometric functions in Excel require values in radians.)  They can learn to define a variable using the name box in Excel: if they change to a new sheet, they will quickly discover that this method is “global” in scope.1 

Just as in coding, students must learn to specify the format of their numerical output. This is important for lab, where we emphasize the use of significant figures. When entering data, they should be including the correct number of significant figures just as they would on a paper data table. Their computed values should reflect the rules of significant figures. They should be able to use scientific notation where appropriate. 

A well-documented code should include intuitive variable names, units of variables, and some specific comments where necessary. The same should be expected on a spreadsheet workbook. We can start out the first few labs with a template that models good code “documentation”. 

 

Plots & Trend lines: Just a bit better

Though many might argue that there are better options for plotting, Excel has a low learning curve and produces quick, useful plots if students understand some basic functionality.  First, they should always use an XY chart to generate a y vs x plot with no lines connecting adjacent data points.  (And they need to know that the “syntax” of plotting in Excel: the first column in a pair is always the x-value if they are highlighting columns!)  Most students can generate a trend line but most don’t know how to edit the numerical format of the trend line equation.  By clicking on the trend line equation in a plot and the selecting the leftmost icon in the “Format Trendline Label”, you can adjust the number format and the number of significant figures (see Figure 1).

example spreadsheet

Figure 1: A trend line formula can be formatted by clicking on equation in the graph and selecting the bar graph icon on the far right.  Then select the desired category of the number including general, scientific, etc.

Final Thoughts

Our population of tudents doesn’t have a lot of experience with spreadsheets.  Excel is readily available and easy to learn.  It’s also incredibly powerful and effective tool for both scientists and engineers.1,2  If you can use Excel spreadsheets, those skills are generally transferable to other spreadsheet programs, e.g. Google Sheets.  Outside of academia, employers value individuals who are proficient in Excel.3 Implementing it as a computational tool in introductory labs is to our students’ advantage and a skill they can put on their resume!  

References

  1. Bernard Liengme and Keith Hekman, Liengme’s Guide to Excel 2016 for Scientists & Engineers: Windows & Mac, 2020, Academic Press.
  2. Robert de Levie, Advanced Excel for Scientific Data Analysis, 3rd , 2012, Atlantic Academic.
  3. https://www.goskills.com/Excel/Articles/Why-learn-excel (Last accessed 07/01/2020)