Using Google Sheets to Aid in BioPchem Problems

I teach an online physical chemistry with a biological focus class at ASU (BCH 341).  This class currently uses standard Homework Sets and online Quizzes (using Blackboard, which ASU requires) as a major component for learning problem solving skills, which is at the heart of BioPchem.  The exams are ‘take-home’ exams and students typically have 24-48 hrs to complete the exam.  So, basically its more like a graded homework set than it is like a traditional University/College exam.  I do this because I do not feel that memorization is useful and I want all the emphasis to be on using ANY and ALL modern resources, technologies, data, etc to solve relevant and practical problems in the area of biological thermodynamics and kinetics.

I often get asked ‘how can I best study for a take-home exam’?  And my typical response is work all the quiz and suggested homework problems assigned and make sure to focus on fully understanding the concepts and techniques for working these problems.  I give this advice because all my exam questions are related in an obvious way to suggested hwk, quizzes and problems I have worked as examples in my lecture notes and/or in video lectures or screencasts.    One method I use to fully understand A LOT of the problems in thermodynamics and kinetics is to basically work the problem in Google Sheets (or any related spreadsheet program, i.e., excel, openoffice calc, gnumeric, and my new favorite coda).   If you use formulas in the cells to do all calculations, then it is really really easy to change a few variables and really see whats going on conceptually for a lot of problems in thermodynamics and kinetics.  Also, the ability to graph/plot data really helps to visualize and conceptualize the problem.  I am inspired by people like 3Brown1Blue who do an amazing job of visualizing all kinds of mathematics.  For me, and I suspect for a lot of us, this visualization really helps with a true conceptual understand of what can often be ‘hard to grasp’ or abstract concepts.

Okay, to a few examples of how I’ve been using Google Sheets to help with biological thermodynamics.   Lets start with ideal and real gases.   The following was a question on a first midterm exam:

Each student has been assigned a common gas or volatile liquid compound.  (A) Do you expect the compound you have been assigned behave like an ideal gas at standard temperature and pressure? (This can be quantified by comparing the pressure of the compound using the Van der Waals equation of state to that of the ideal gas equation of state) (B) At what molar volume does the compound you have been assigned deviate substantially from ideality (i.e, from the behavior that would be expected based on the ideal gas law)? (This can be quantified by determining what conditions cause a > 5% deviation from the ideal gas equation of state)BCH341_Exam1_Problem7_GoogleSheet_ScreenShot

Making a spreadsheet where you can quickly and easily change the two VDW gas constants and see the new values and plot is great for grading.  However, it would also be great for students to use this method.  If they would have coded this up when working similar homework problems, the take home exam question above would have taken less than a minute to enter the 2 values for the gas assigned and copy their data and plot onto the exam (or just share their google sheet for assessment/grading).  Also, this is the type of spreadsheet you can come back to as a template for more advancing plotting of thermodynamic gas problems and using the VDW Equation of State for looking at the liquid-gas phase transition and critical point phenomenology.   A copy of the ‘gsheet’ is provided here: BCH341 Exam 1 Problem 7 – Spring  The ‘gsheet’ has been zipped to make it easy to download as a single file.

Next, how about a common thermodynamic phase transition problem on the compound that is most essential to biological life, H2O.  The HWK Set 2 problem states:2-1A_Plot

Calculate the change in energy for the process of taking an ice cube (typical ice cube is 1 in3) out of your freezer at -20°C and heating at atmospheric pressure to a final state of steam at 120°C.  It is very useful to look at this process from several thermodynamic perspectives.  Hence, it is suggested that you not only look at the various types of energy change that occurs in this common physical change process, but you also look at the change in entropy.  Ice_Cube_Melting

Again, a google sheet really helps with not only the calculation, but more importantly a plot to help visualize the energies and/or entropy changes involved in melting and boiling of H2O.


One last example… Decarboxylation reactions are so important in biological sciences, medicine and chemistry.  A great fundamental system to look at is magnesium carbonate calcining.  Where does this reaction become energically or thermodynamically favorable (Where does ΔG go negative)?  Again, a plot really helps conceptualize how the energy of this reaction changes with temperature:


And I’m leaving out the best part about using Google Sheets… you can collaborate with fellow students, you can share it with anyone had get their input or comments or edits.  You can build on existing sheets to add more complex concepts, plots, calcuations, etc.

Okay, I’ll stop… but I hope this motivates some BCH341 students to work assigned problems using google sheet or any collaborative spreadsheet or computational program, i.e., mathematica, maple, scilab, etc…. Happy problem solving!! – Jeff Yarger




Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this:
search previous next tag category expand menu location phone mail time cart zoom edit close