{"id":5860,"date":"2017-09-07T14:40:59","date_gmt":"2017-09-07T19:40:59","guid":{"rendered":"https:\/\/www.kabt.org\/?p=5860"},"modified":"2017-09-07T14:40:59","modified_gmt":"2017-09-07T19:40:59","slug":"plotting-error-bars-in-google-sheets-on-a-scatter-plot","status":"publish","type":"post","link":"https:\/\/www.kabt.org\/?p=5860","title":{"rendered":"Plotting Error Bars in Google Sheets?…..on a scatter plot????"},"content":{"rendered":"

Robbyn Tuinstra, tri-athlete and AP Bio teacher extraordinaire recently had a question about putting error bars on scatter plot data in Google Sheets.\u00a0\u00a0<\/span>Several of us weighed in—a couple of us suggested it wasn\u2019t possible, a couple of others pointed to a video where custom error bars were placed on a bar graph.\u00a0\u00a0<\/span>I mentioned that I had tried before to do this but gave up since I use other tools like Excel, Plotly and various stat programs.\u00a0\u00a0<\/span>Still this issue festered for a while and I finally had to try and attack it again.\u00a0\u00a0<\/span>I was partially successful.\u00a0\u00a0\u00a0<\/span>I\u2019ll describe what I have discovered but this also provides an opportunity to revisit suggested quantitative goals that the community might want to work towards.<\/span><\/p>\n

First the type of experiment\/data appropriate to this question.\u00a0\u00a0<\/span>Last year I produced a series of posts that featured a lengthy coverage of the types of data analysis and model application one might want to consider when doing a very simple lab–the yeast catalase floating disk lab.\u00a0\u00a0<\/span>You can find these posts on the Kansas Association of Biology Teachers Bioblog:\u00a0\u00a0<\/span>https:\/\/www.kabt.org\/2017\/02\/06\/summary-post-for-teaching-quantitative-skills\/<\/a><\/span><\/p>\n

I didn\u2019t use google sheets in these posts but I will here.\u00a0\u00a0<\/span>Here is a data table of results that has already been transposed from disk rise time to rate of disk rise in floats per second.<\/span><\/p>\n

<\/p>\n

This data table is typical of how we might record this types of data.\u00a0\u00a0<\/span>In the original postings I talked about how to plot this data and to do a curve fit.\u00a0\u00a0<\/span>Here\u2019s one way to plot this data (in excel) using approx. 95% error bars (2 x SEM).<\/span><\/p>\n

<\/p>\n

I think this is the type of data and plot that Robbyn was talking about.\u00a0\u00a0\u00a0<\/span>The model for enzyme kinetics is known as the Michaelis-Menten equation and it can be used to fit the data.\u00a0\u00a0<\/span>I\u2019m not sure we want to get into that in the AP Bio classroom but perhaps we do.\u00a0\u00a0<\/span>Nevertheless, I think we definitely should consider having students at least generate the graph.\u00a0\u00a0<\/span>The error bars are nice but I think when it comes to developing student argumentation from evidence that simply plotting all the data points along with the means is sufficient.\u00a0\u00a0<\/span>A plot that looks like this in Excel:<\/span><\/p>\n


\n<\/span><\/p>\n

How do we do this in Google Sheets?<\/span><\/p>\n

One of the first things to do to make this easier to plot is to change the data table into something like this:<\/span><\/p>\n


\n<\/span><\/p>\n

Note that there is a column for the data points and a separate column for the means. This allows us to plot two dependent variable series on the graph.\u00a0\u00a0<\/span>We\u2019ll use this strategy later.\u00a0\u00a0<\/span>Note that I have also added a 2 % substrate concentration and a 0% substrate concentration but I have left the rise time blank for these.\u00a0\u00a0<\/span>These x variables extend the range of of the x axis when we plot.\u00a0<\/span><\/span><\/p>\n

Select these columns, choose Insert Graph and change to a scatter plot you end up with a plot that looks like this:<\/span><\/p>\n

<\/p>\n

Here I\u2019ve changed the size and color of the individual data points.<\/span><\/p>\n


\n<\/span><\/p>\n

I won\u2019t go into modifying your lablels, axis titles and titles.<\/span><\/p>\n

Personally, I think this is more than adequate evidence to make the argument about the shape of this curve but I imagine in my classes we\u2019d go for a non-linear curve fit (to help them justify the upper end math classes they are taking)<\/span><\/p>\n

But perhaps, like Robbyn you want to include error bars instead of the data points for each substrate concentration.\u00a0\u00a0<\/span>This really doesn\u2019t seem to be possible with simple menu options in Google Sheets.\u00a0\u00a0<\/span>(obviously, if you want to get into programming, it would be possible).\u00a0\u00a0<\/span>I did however find this work around.<\/span><\/p>\n

First let\u2019s change the data table again. Lets add a new column that has a calculated 2 x standard error of the means.\u00a0\u00a0<\/span>And another new column that includes values for [mean + (2 x SEM)] and [mean – (2 x SEM).]\u00a0\u00a0<\/span>Now the table looks like this:<\/span><\/p>\n


\n<\/span><\/p>\n

Highlight the entire table, insert a chart BUT here is the thing.\u00a0\u00a0<\/span>If you highlight the data and let Google sheets determine the graph type it will pick Line Graph.\u00a0\u00a0<\/span>Let it this time.\u00a0\u00a0<\/span>That is key to what we need to draw the error bars.\u00a0\u00a0<\/span>You get something like this:<\/span><\/p>\n

<\/p>\n

We have too many variables plotted.\u00a0\u00a0<\/span>We don\u2019t need the individual data points now so we\u2019ll get rid of those.\u00a0\u00a0<\/span>We will also turn off the plotting of the SEM (but not the plus or minus SEM).\u00a0\u00a0<\/span>Finally, select, use column A for labels (assuming you\u2019ve put your substrate concentrations in column A.<\/span><\/p>\n


\n<\/span><\/p>\n

Once that is done, we should be down to something that looks like this.\u00a0\u00a0<\/span>One variable plotted is the means and along with a line that connect plus 2 x SEM to minus 2 x SEM….<\/span><\/p>\n

<\/p>\n

There you have it—a work around that works because by default Google sheets treats the blank cells in the plus or minus columns as null data–not zeros.\u00a0\u00a0<\/span><\/span><\/p>\n

p.s.<\/span><\/p>\n

You can turn off that feature and the graph will look like this:<\/span><\/p>\n


\n<\/span><\/p>\n

Obviously not what we want.\u00a0\u00a0<\/span><\/span><\/p>\n","protected":false},"excerpt":{"rendered":"

Robbyn Tuinstra, tri-athlete and AP Bio teacher extraordinaire recently had a question about putting error bars on scatter plot data in Google Sheets.\u00a0\u00a0Several of us weighed in—a couple of us suggested it wasn\u2019t possible, a couple of others pointed to a video where custom error bars were placed on a bar graph.\u00a0\u00a0I mentioned that I
+ Read More<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_s2mail":"yes","_uf_show_specific_survey":0,"_uf_disable_surveys":false,"footnotes":""},"categories":[1],"tags":[],"class_list":["post-5860","post","type-post","status-publish","format-standard","hentry","category-kabt-news"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.kabt.org\/index.php?rest_route=\/wp\/v2\/posts\/5860","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.kabt.org\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.kabt.org\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.kabt.org\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.kabt.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=5860"}],"version-history":[{"count":1,"href":"https:\/\/www.kabt.org\/index.php?rest_route=\/wp\/v2\/posts\/5860\/revisions"}],"predecessor-version":[{"id":5861,"href":"https:\/\/www.kabt.org\/index.php?rest_route=\/wp\/v2\/posts\/5860\/revisions\/5861"}],"wp:attachment":[{"href":"https:\/\/www.kabt.org\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=5860"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.kabt.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=5860"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.kabt.org\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=5860"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}