====== How To Make Your Own Google Motion Chart: ======
=== Australian Curriculum: ===
The NSW Sylabusses for the Australian curriculum requires that students learn how to describe and represents mathematical situations in a variety of ways using mathematical terminology and conventions [[http://syllabus.bos.nsw.edu.au/mathematics/mathematics-k10/outcomes-k10/|MA3-1WM,MA3-3WM,MA3-18SP]]
=== Resources : ===
There are two primary sources of teacher information for this project, plus three student resources:
Teacher resources:
- A [[http://web1.wahroonga-p.schools.nsw.edu.au/teachers/doku.php?id=collaboration:colour-by-numbers|Colour By Numbers]] activity plan for teachers.
- A [[http://web1.wahroonga-p.schools.nsw.edu.au/teachers/doku.php?id=howtos:google-charts| Google Charts Howto]] for teachers.
Student resources:
- How to [[http://web1.wahroonga-p.schools.nsw.edu.au/students/doku.php?id=howtos:google-charts|make your own Google Motion Charts]] for students (and teachers).
- An [[http://web1.wahroonga-p.schools.nsw.edu.au/students/doku.php?id=homework:google-charts-quiz|on-line quiz/pretest and Motion Chart howto]] for students (and teachers).
- A [[http://web1.wahroonga-p.schools.nsw.edu.au/students/doku.php?id=homework:google-charts-data|on-line data entry form for eye colour project]].
====== Project: ======
This project is intended to provide a fun opportunity for students to learn something new about maths and genetics through the use of modern charting and data visualisation tools.
The information below will help introduce students to Google Motion Charts (a homework task), and to help students learn how to collect/manage data and create their own charts.
This project provides students with an opportunity to look at data and mathematics in a new way.
====== Introduction To Motion Charts: ======
Charts are visual displays that are designed to make it easier for people to understand quantities and the relationships between things.
Google Apps includes [[https://developers.google.com/chart/interactive/docs/gallery| a large collection of modern mathematical processing and charting tools]].
The 'Motion Chart' is one of these apps.
A [[https://support.google.com/docs/answer/1047434?hl=en&ref_topic=30240|Motion Chart]] is a dynamic chart that displays and compares up to five data sets and tracks them over the course of time:
{{ youtube>jbkSRLYSojo?640x360 |Data Visualisation }}
=== Video 1. Example Data Visualisation Using Motion Charts (what is possible): ===
Google Apps includes [[https://developers.google.com/chart/interactive/docs/gallery| a large collection of charting options]]. Many of the Google charting options are easy enough for primary students use and this project provides a fun opportunity for students to put their charting skills to the test.
----
====== How To create a Motion Chart Spreadsheet and/or Form: ======
The first column should contain entities (e.g. countries), the second is time (e.g. years), followed by 2-4 numeric or string columns
See [[https://support.google.com/docs/answer/1047434?hl=en|Google Motion Chart howto]]:
- Either [[https://support.google.com/docs/topic/20329?hl=en&ref_topic=20322|create a new Google spreadsheet]] ...OR... a [[https://support.google.com/docs/answer/87809?hl=en|form/spreadsheet combination]].
- Add [[https://support.google.com/docs/answer/1047434?hl=en|four data entry columns to your spreadsheet/form]]
- Decide what you want to show as bubbles (e.g. Eye colours) put those in column 'A'
- The second column MUST include DATE values, either in year, month/day/year, week number or quarter format. If linking a form to a sheet, the 'Timestamp' data should be located [https://support.google.com/docs/answer/1047434?hl=en|in second column]] (column ''B'').
- There can be a minimum of two and maximum of four additional columns (maximum total six columns including Timestamp/Date column). These columns can contain either numeric or text data. Text data, for example, could be an indicator of the weather for a given day -- "cloudy", "raining", or "clear". Columns that display numeric data will be available for selection in the chart options box for the 'X', 'Y', 'Color' and 'Size' axes of the motion chart. Columns containing text will only appear in the drop-down menu for 'Color'.
NOTE : When a spreadsheet is created by Google forms, Google forms adds a default ''Timestamp'' column. If you create a spreadsheet manually, then add the Timestamp column manually (if it is missing)
In case of problems, the column layout and ''Timestamp'' columns are the most likely culprits.
Here is an example of column entries for a working spreadsheet:
{{ :howtos:wps-eye-colour-motion-chart-final-583x174.jpg |}}
** Fig 1. Example [[https://docs.google.com/spreadsheets/d/1nCJ5Fiy2n7CDPLCi3MkQ1KF75VMUjOWkJmZg6qlFi6A/edit?usp=sharing|spreadsheet entries for a working demo Motion Chart]]**
----
====== Create The Example Interactive Motion Chart: ======
To get up and running with live testing:
- Add a few test entries to the spreadsheet (three or four rows is enough
- From the sheet menu, click on: ''Insert -> Chart''
- Click on the ''Insert'' button in bottom left-hand corner of the motion chart screen
- When done, select to option to save the chart in it's own sheet (see docs)
- See section (below) titled 'Motion Chart Setup Details' for more detail.
{{ :howtos:wps-eye-colour-motion-chart-final-02-350x191.jpg |}}
** Fig 3. Example [[https://docs.google.com/spreadsheets/d/1nCJ5Fiy2n7CDPLCi3MkQ1KF75VMUjOWkJmZg6qlFi6A/edit?usp=sharing|Eye Colour spreadsheet]] for Motion Chart **
----
{{ :howtos:chart-move-to-own-sheet-google-docs.jpg |}}
** Fig 3. Move new Motion Chart [[https://docs.google.com/spreadsheets/d/1nCJ5Fiy2n7CDPLCi3MkQ1KF75VMUjOWkJmZg6qlFi6A/edit?usp=sharing|to own sheet]] **
----
A data entry form has been created (ask your teacher for the link) to allow students to enter their own data, mix-and-match data categories and investigate a variety of motion graphics within a single spreadsheet.
* **Example Graph 1:** [[https://docs.google.com/spreadsheets/d/1nCJ5Fiy2n7CDPLCi3MkQ1KF75VMUjOWkJmZg6qlFi6A/edit?usp=sharing|Eye Colour Maths Project]]
* **Example Graph2:** WPS Mailchimp newsletter, showing [[https://docs.google.com/spreadsheets/d/1DfGFmFvV-BcQaBk2cirn7I-YrhL7LfSLdXosZgxNrlg/edit?usp=sharing|recipients by click rate and newsletter version number]] for month FEB 2015.
----
----
====== How To Display Data & Experiment with Motion Chart Display Settings ======
Check out the [[https://docs.google.com/forms/d/1LyeNdTyWwg836UmLRBBTJ_8DifQ8RWLYZ9SsFhR85rQ/viewform?usp=send_form|example working form and spreadsheet combo]].
=== Key: See numbered green arrows in screen-shot below ===
The display can be manipulated using a wide range of settings as per green arrows in screen-grabs below.
{{ :howtos:wps-eye-colour-motion-chart-final-03.jpg |}}
** Fig 5. Example [[https://docs.google.com/spreadsheets/d/1nCJ5Fiy2n7CDPLCi3MkQ1KF75VMUjOWkJmZg6qlFi6A/edit?usp=sharing|Eye Colour spreadsheet]] **
----
=== Customise graph settings by adjusting controls hi-lighted by green arrows (see above): ===
**KEY VALUES:** Each number in list below, corresponds with a numbers on green arrows above. Each chart display can (and should be) be highly customised.
- Select the type of chart displayed: Bubble, bar or zig-zag graph TAB options
- Click to open display - The bar chart display can be customised when open
- Click to open display - The line chart display can be customised when open
- Choose how colours are allocated to the items displayed in chart (usually best to select 'Unique Colors')
- Choose to display all bubbles same size or bubble size in proportion to selected value
- Select one or more checkboxes to turn on/off various item information bubbles
- Select 'trails' to leave a trail showing history (for animated charts only)
- Display a trail of values plotted (in this example, for 'green' eye colour values).
- Change displayed X axis scale between 'linear' or 'log' - Also select 'Time' as the value to display on X axis)
- Set and freeze the time-line by dragging the play 'progress bar' (slider) left-right
- Display the values that have been entered into the spreadsheet via the on-line form
- Display the values that have been entered into the master spreadsheet (ABS CAS Data set)
- Display 'Chart 1' (ABS Data Motion Chart) or 'Chart 2' (Motion Chart for student-entered data set)
- An interactive slider button to right of Play button controls playback speed
- Play or Pause Motion Chart animation
- Select the data set to display on Y axis via the 'pullout' menu options (see item #9 to choose items displayed on X axis)
- Display raw sheet value(s) when mouse over item (in this case, item #18)
- Toggle X axis scale between 'linear' or 'log'
Hover mouse over any of the settings - most (but not all) have some effect.
----
{{ :howtos:wps-eye-colour-motion-chart-final-05.jpg |}}
** Fig 6. Example [[https://docs.google.com/spreadsheets/d/1nCJ5Fiy2n7CDPLCi3MkQ1KF75VMUjOWkJmZg6qlFi6A/edit?usp=sharing|Eye Colour spreadsheet]]**
----
Otionally, Students may use an on-line form to enter and analyse additional data that they may collect from the "Colour By Numbers" project. Students may elect to create their own, personal Motion Charts using an alternative data set (spreadsheet) of their own.
{{ :howtos:wps-eye-colour-motion-chart-final-04.jpg |}}
** Fig 4. Example [[https://docs.google.com/spreadsheets/d/1nCJ5Fiy2n7CDPLCi3MkQ1KF75VMUjOWkJmZg6qlFi6A/edit?usp=sharing|Eye Colour spreadsheet]] **
----
Chart/options makes a huge difference to the understandibility of the display.
For example, for "Top30 Countries" graph (above) it may not seem to make sense to display 'Persons' on both X and Y axis (also set 'Size'='Persons' and "Color'='Unique Color' and X axis scale to 'Log' instead of 'Lin'(ear))... but try it and see!
Also, viewing the same data in different chart types will high-light different trends.
=== Pre-test, Base-line Charts & Maths Quiz: ======
Check out the "Colour By Numbers', Flipped Classroom [[https://docs.google.com/forms/d/1wzH0g9tRgkvOv3nm6_VHzcTcPjf2vjzTaRCFyA9Je1s/viewform|Student Homework & Assessment Task]] (Google form-based [[http://www.flubaroo.com|Flubaroo]] quiz).
=== Colour By Numbers - Data Entry Form For LabGroups: ===
Check out the 'Colour By Numbers', Flipped Classroom maths project [[https://docs.google.com/forms/d/1LyeNdTyWwg836UmLRBBTJ_8DifQ8RWLYZ9SsFhR85rQ/viewform?usp=send_form|data entry form]]: For entering Labgroup data only.
All of the on-line tools discussed here are designed and supported by third parties.
Providing detailed instruction in the use of these tools is beyond the scope of this document
Please refer to Goggle on-line knowledge base for more detail.
The following data is technical only - It can be ignored.
----
====== TechnoBabble: ======
=== Other Chart Formats ===
* Example Mailchimp Deliver Stats via [[https://docs.google.com/spreadsheets/d/19czdSb1wzjEFACsV0aM3uhe6-WIZ-LbQIwvhuYF1u9E/edit?usp=sharing|AreaMap]]
* Proper use of [[https://drive.google.com/previewtemplate?id=0AoFkkLP2MB8kdHVSUFI1NEwtNHFWNzRCVlVnUUE0a1E&mode=public&gid=10|TreeMaps]]
* Mailchimp Stats using [[https://docs.google.com/spreadsheets/d/1Fhyf_N2Of07TxLZJqMEefWveNL6s2rrXcVhZm4OyF6o/edit?usp=sharing|TreeMap]]
=== Links ===
* Here is an [[http://comm217.stanford.edu/2014/02/week-6-data-visualizations-using-google-drive-and-fusion-tables/|example guide published by Stanford University]].
* Ideas on [[http://www.smallbusinesscomputing.com/biztools/5-knock-em-dead-google-gadgets.html|how to use charts]]
* Google AddOns: https://developers.google.com/apps-script/add-ons/
----
----
====== MORE BABBLE - ABSOLUTELY NOT WORTH READING BELOW HERE: ======
=== Other Chart Formats ===
* Example Mailchimp Deliver Stats via [[https://docs.google.com/spreadsheets/d/19czdSb1wzjEFACsV0aM3uhe6-WIZ-LbQIwvhuYF1u9E/edit?usp=sharing|AreaMap]]
* Proper use of [[https://drive.google.com/previewtemplate?id=0AoFkkLP2MB8kdHVSUFI1NEwtNHFWNzRCVlVnUUE0a1E&mode=public&gid=10|TreeMaps]]
* Mailchimp Stats using [[https://docs.google.com/spreadsheets/d/1Fhyf_N2Of07TxLZJqMEefWveNL6s2rrXcVhZm4OyF6o/edit?usp=sharing|TreeMap]]
=== Links ===
* Here is an [[http://comm217.stanford.edu/2014/02/week-6-data-visualizations-using-google-drive-and-fusion-tables/|example guide published by Stanford University]].
* Ideas on [[http://www.smallbusinesscomputing.com/biztools/5-knock-em-dead-google-gadgets.html|how to use charts]]
* Google AddOns: https://developers.google.com/apps-script/add-ons/
----
====== Google Sheet Functions ======
* Simple guide (by topic) to managing Google Sheets: http://www.gcflearnfree.org/googlespreadsheets
* How to create functions in Google Sheets: http://spreadsheetpro.net/how-to-make-sumifs-countifs-averagifs-functions-in-google-spreadsheet/
* Infinite Copy down: http://stackoverflow.com/questions/19039665/make-google-spreadsheet-formula-repeat-infinitely
=== Eye Colour Motion Chart Configuration ===
The Motion Chart plots change or trend over time.
Eye colours do not change (much) over time, so we create a 'sequence' of events instead of 'times' of events. To do that, we create a function to create an event sequence (Class/Year numbers) into a time formatted column.
=ARRAYFORMULA(D8:D500+2000)
Each iteration of the results provides the number of a particular eye-colours found in a particular result set. These numbers will vary rather than trending.
To provide some kind of meaningful information, all of the results are averaged and displayed in the final screen when the motion chart completes (the first seven rows of the sheet are reserved for 21015 entries - which are outside of form entry range.
* We also "Freeze" the first eight rows - **must freeze all rows that contain functions!**
* Format the TotalColour column to display 2 dec places
=AVERAGE(FILTER(C8:C,A8:A="Blue"))
=AVERAGE(FILTER(C8:C,A8:A="Brown"))
=AVERAGE(FILTER(C8:C,A8:A="Green"))
=AVERAGE(FILTER(C8:C,A8:A="Grey"))
=AVERAGE(FILTER(C8:C,A8:A="Hazel"))
=AVERAGE(FILTER(C8:C,A8:A="Other"))
{{:howtos:wps-eye-colour-sheet-functions-01.jpg|}}
=== Fig 1 Sheet overview ===
----
{{:howtos:wps-eye-colour-sheet-functions-02.jpg|}}
=== Fig 2 Sheet Column Numbering/Year Function ===
----
{{:howtos:wps-eye-colour-sheet-functions-03.jpg|}}
=== Fig 3 Sheet Ey Colour Averaging Function ===
----
=== Custom Spreadsheet Functions ===
Google Spreadsheet functions list: https://support.google.com/docs/table/25273?hl=en
Cumulatively sum only numbers that belong to a certain username (string) in a separate column? I'd like to use arrayformula so cells autofill with data without dragging formula manually. E.g. running total for user A:
User Amount Running Total for A
A 1 1
B 2
A 4 5
A 3 8
B 5
A 2 10
If the data starts in row 2, then try:
=ArrayFormula(IF(A2:A="A",SUMIF(IF(A2:A="A",ROW(A2:A),ROWS(A:A)+1),"<="&ROW(A2:A),B2:B),))
Blue:
=ArrayFormula(IF(D2:D="Blue",SUMIF(IF(D2:D="Blue",ROW(D2:D),ROWS(E:E)+1),"<="&ROW(D2:D),E2:E),))
Brown:
=ArrayFormula(IF(D3:D="Brown",SUMIF(IF(D3:D="Brown",ROW(D3:D),ROWS(E:E)+1),"<="&ROW(D3:D),E3:E),))
Green:
=ArrayFormula(IF(D4:D="Green",SUMIF(IF(D4:D="Green",ROW(D4:D),ROWS(E:E)+1),"<="&ROW(D4:D),E4:E),))
http://webapps.stackexchange.com/questions/69778/arrayformula-to-compute-running-average-for-groups-of-rows
Brown:
=SUM(FILTER(E:E,D:D="Brown"))
Green:
=SUM(FILTER(E:E,D:D="Green"))
====== Charting Google Form Responses ======
Sharing data between sheets:
* Same SSheet: http://computers.tutsplus.com/tutorials/turn-your-google-docs-form-responses-into-beautiful-visualizations--cms-21520
* Other SSheet: http://webapps.stackexchange.com/questions/43085/reference-data-from-another-spreadsheet-in-google-docs-drive
* 'Data Everywhere' - Google AddOn: http://support.dataeverywhere.com/article/49-sharing-data-between-multiple-google-sheets-documents
====== Base-line Assessment Form, Orientation & Homework Quiz ======
http://www.makeuseof.com/tag/how-to-use-google-forms-to-create-your-own-self-grading-quiz/
Prepare a Google Form to provide an [[https://docs.google.com/forms/d/1wzH0g9tRgkvOv3nm6_VHzcTcPjf2vjzTaRCFyA9Je1s/viewform?usp=send_form|on-line assessment and self-grading quiz]].
The [[https://docs.google.com/forms/d/1wzH0g9tRgkvOv3nm6_VHzcTcPjf2vjzTaRCFyA9Je1s/viewform?usp=send_form|example quiz]] used the Google Sheet Add-On [[http://www.flubaroo.com/flubaroo-user-guide|Flubaroo]] - A free tool that helps quickly grade multiple-choice and similar assignments:
* Computes average assignment score.
* Computes average score per question, and flags low-scoring questions.
* Displays a grade distribution graph.
* Option to email each student their grade, and an answer key.
* Ability to send individualized feedback to each student.
More detail is available on [[http://www.alicekeeler.com/teachertech/2013/03/10/creating-a-quiz-with-a-google-form-and-use-flubaroo-to-grade-it/|how to set up a Flubaroo quiz]]
{{ :howtos:wps-eye-colour-quiz-02.jpg |Google Form with Flubaroo Student Responses}}
**Fig. Google form with [[https://docs.google.com/spreadsheets/d/14yi7rm2Koj1u166HO6lysyQrm1u7wu1Dxn_SvhW6rf4/edit?usp=sharing|Student Response Sheet with Flubaroo Grades sheet]]**
====== Transpose Rows & Columns ======
In LibreOffice Calc, there is a way to "rotate" a spreadsheet so that rows become columns and columns become rows.
- Select the cell range that you want to transpose.
- Choose Edit - Cut.
- Click the cell that is to be the top left cell in the result.
- Choose Edit - Paste Special.
- In the dialog, mark Paste all and Transpose.
- If you now click OK the columns and rows are transposed.