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 MA3-1WM,MA3-3WM,MA3-18SP

There are two primary sources of teacher information for this project, plus three student resources:

Teacher resources:

1. A Colour By Numbers activity plan for teachers.
2. A Google Charts Howto for teachers.

Student resources:

1. How to make your own Google Motion Charts for students (and teachers).
2. An on-line quiz/pretest and Motion Chart howto for students (and teachers).
3. A on-line data entry form for eye colour 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.

Charts are visual displays that are designed to make it easier for people to understand quantities and the relationships between things.

Google Apps includes a large collection of modern mathematical processing and charting tools.

The 'Motion Chart' is one of these apps.

A Motion Chart is a dynamic chart that displays and compares up to five data sets and tracks them over the course of time:

Google Apps includes 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.

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 Google Motion Chart howto:

1. Either create a new Google spreadsheet …OR… a form/spreadsheet combination.
2. Add four data entry columns to your spreadsheet/form
3. Decide what you want to show as bubbles (e.g. Eye colours) put those in column 'A'
4. 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).
5. 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:

Fig 1. Example spreadsheet entries for a working demo Motion Chart

To get up and running with live testing:

1. Add a few test entries to the spreadsheet (three or four rows is enough
2. From the sheet menu, click on: Insert → Chart
3. Click on the Insert button in bottom left-hand corner of the motion chart screen
4. When done, select to option to save the chart in it's own sheet (see docs)
5. See section (below) titled 'Motion Chart Setup Details' for more detail.

Fig 3. Example Eye Colour spreadsheet for Motion Chart

Fig 3. Move new Motion Chart 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: Eye Colour Maths Project
• Example Graph2: WPS Mailchimp newsletter, showing recipients by click rate and newsletter version number for month FEB 2015.

Check out the example working form and spreadsheet combo.

The display can be manipulated using a wide range of settings as per green arrows in screen-grabs below.

Fig 5. Example Eye Colour spreadsheet

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.

1. Select the type of chart displayed: Bubble, bar or zig-zag graph TAB options
2. Click to open display - The bar chart display can be customised when open
3. Click to open display - The line chart display can be customised when open
4. Choose how colours are allocated to the items displayed in chart (usually best to select 'Unique Colors')
5. Choose to display all bubbles same size or bubble size in proportion to selected value
6. Select one or more checkboxes to turn on/off various item information bubbles
7. Select 'trails' to leave a trail showing history (for animated charts only)
8. Display a trail of values plotted (in this example, for 'green' eye colour values).
9. Change displayed X axis scale between 'linear' or 'log' - Also select 'Time' as the value to display on X axis)
10. Set and freeze the time-line by dragging the play 'progress bar' (slider) left-right
11. Display the values that have been entered into the spreadsheet via the on-line form
12. Display the values that have been entered into the master spreadsheet (ABS CAS Data set)
13. Display 'Chart 1' (ABS Data Motion Chart) or 'Chart 2' (Motion Chart for student-entered data set)
14. An interactive slider button to right of Play button controls playback speed
15. Play or Pause Motion Chart animation
16. Select the data set to display on Y axis via the 'pullout' menu options (see item #9 to choose items displayed on X axis)
17. Display raw sheet value(s) when mouse over item (in this case, item #18)
18. Toggle X axis scale between 'linear' or 'log'

Hover mouse over any of the settings - most (but not all) have some effect.

Fig 6. Example 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.

Fig 4. Example 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.

Check out the “Colour By Numbers', Flipped Classroom Student Homework & Assessment Task (Google form-based Flubaroo quiz).

Check out the 'Colour By Numbers', Flipped Classroom maths project 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.

• Example Mailchimp Deliver Stats via AreaMap
• Proper use of TreeMaps
• Mailchimp Stats using TreeMap

• Here is an example guide published by Stanford University.
• Ideas on how to use charts
• Google AddOns: https://developers.google.com/apps-script/add-ons/

• Example Mailchimp Deliver Stats via AreaMap
• Proper use of TreeMaps
• Mailchimp Stats using TreeMap

• Here is an example guide published by Stanford University.
• Ideas on how to use charts
• Google AddOns: https://developers.google.com/apps-script/add-ons/

• 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

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"))

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"))

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

http://www.makeuseof.com/tag/how-to-use-google-forms-to-create-your-own-self-grading-quiz/

Prepare a Google Form to provide an on-line assessment and self-grading quiz.

The example quiz used the Google Sheet Add-On 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 how to set up a Flubaroo quiz

Fig. Google form with Student Response Sheet with Flubaroo Grades sheet

In LibreOffice Calc, there is a way to “rotate” a spreadsheet so that rows become columns and columns become rows.

1. Select the cell range that you want to transpose.
2. Choose Edit - Cut.
3. Click the cell that is to be the top left cell in the result.
4. Choose Edit - Paste Special.
5. In the dialog, mark Paste all and Transpose.
6. If you now click OK the columns and rows are transposed.