Tutorial:Spreadsheet View and Basic Statistics Concepts

From GeoGebra Manual
Jump to: navigation, search


Introduction to GeoGebra’s Spreadsheet View

Spreadsheet Cells Input

In GeoGebra’s Spreadsheet View every cell has a specific name that allows you to directly address each cell. For example, the cell in column A and row 1 is named A1.
Note: These cell names can be used in expressions and commands in order to address the content of the corresponding cell.

You can not only use numbers in spreadsheet cells, but all types of mathematical objects that are supported by GeoGebra (e.g., coordinates of points, functions, lines). If possible, GeoGebra immediately displays the graphical representation of the object you enter into a spreadsheet cell in the Graphics View as well. Thereby, the name of the object matches the name of the spreadsheet cell used to initially create it (e.g., A5, C1).

Note: By default, spreadsheet objects are classified as auxiliary objects in the Algebra View. You can show or hide these auxiliary objects by selecting Auxiliary Objects from the styling bar at the top of the Algebra View.

Customize the User Interface and Toolbar

The user interface of GeoGebra can be customized by using the View menu. For example, you can show different parts of the interface (e.g. the Spreadsheet view) by checking the corresponding menu item in the View menu (e.g. Spreadsheet).

Spreadsheet1.PNG

Record to Spreadsheet Feature

Preparations

Construction Steps

1 Tool Slider.gif Create a slider a with default interval and increment 1.
Note Hint: Select tool Slider and click in the Graphics View to set the position for the slider. In the appearing dialog window change the increment to 1 and click the Apply button.
2 Create point A by entering A = (a, 2a) into the Input Bar.
Note Hint: The value of slider a determines the x-coordinate of point A while the y-coordinate is a multiple of this value.
3 Tool Show Hide Label.gif Show the label of point A in the Graphics View.
4 Tool Move.gif Change the value of slider a to examine different positions of point A.
5 Tool Move Graphics View.gifTool Zoom In.gifTool Zoom Out.gif Use tools Move Graphics View, as well as Zoom In and Zoom Out to adjust the visible part of the Graphics View and make point A visible in all positions.
6 Tool Record to Spreadsheet.gif Record the coordinates for different positions of point A to the spreadsheet:
  1. Select tool Record to Spreadsheet. Then, click on point A in order to highlight it.
    Note: The coordinates for the actual position of point A are immediately entered into cells A1 (x-coordinate) and B1 (y-coordinate) of the spreadsheet.
  2. Now, change the value of slider a in order to record the coordinates of all other possible positions of point A to the spreadsheet as well.
    Note: Do not switch to another tool before moving the slider.

Tasks

Task 1:Examine the pattern of y-values in column B

You could give this construction to your students and let them explore the pattern in column B, which is created by the y-coordinates of different positions of point A. Encourage your students to make a prediction about a function graph that runs through all different positions of point A. Have your students enter the corresponding function into the Input bar in order to check if their prediction was correct (e.g. students enter f(x) = 2x to create a line through all points).

Task 2: Create a new problem

Change the y-coordinate of point A in order to create a new problem:

  • Right click (MacOS: Ctrl-click) on point A and select Object Properties… from the appearing context menu.
  • In tab Basic you can change the y-coordinate of point A in the text field Definition to, for example, a^2.
  • Use the other tabs of the Properties dialog in order to change the color (tab Color) or size (tab Style) of point A.
  • Close the Properties dialog when you have made all desired changes.
  • Repeat steps 7 to 9 of the instructions above in order to record the coordinates of the new positions of point A to the spreadsheet.
    Note: If you didn’t delete the old values in columns A and B, GeoGebra automatically uses the next two empty columns (e.g. columns C and D) in order to record the new values for x-coordinates and y-coordinates.

Relative Copy and Linear Equations

Preparations

Construction Steps

1 Tool Move Graphics View.gif Activate tool Move Graphics View and drag the origin of the coordinate system close to the lower left corner of the Graphics View.
2 In the Spreadsheet View, click on cell A1 enter the point coordinates (0, 0).
3 In the Spreadsheet View, click on cell A2 enter the point coordinates (1, 1).
4 Tool Show Hide Label.gif Show the labels of both points in the Graphics View.
5 Tool Move.gif Relative copy the inserted point coordinates to other cells in column A:
  1. Highlight both cells A1 and A2 by using the mouse.
  2. Click on the little square at the lower right corner of the highlighted cell range.
  3. Hold the mouse button down and drag the pointer down to cell A11.
6 Tool Move Graphics View.gifTool Zoom In.gifTool Zoom Out.gif Use tools Move Graphics View, as well as Zoom In and Zoom Out to adjust the visible part of the Graphics View and make point A visible in all positions.

Task 1: Examine the coordinates of the point sequence

What sequence of numbers is created if you apply the "relative copy" feature of the GeoGebra spreadsheet the way it is described above?

Note Hint: Examine the x-coordinates of all created points and come up with a conjecture about how they are related. Then, check your conjecture using the ycoordinates of the points.


Task 2: Find the matching equation

Make a prediction about an equation that would create a graph going through all points of this sequence. Enter this equation into the Input bar in order to check your prediction.

Task 3: Create a new problem

Change the coordinates of the initial points in order to create a sequence of points that can be examined by your students.

Version 1: Change the initial points in the Spreadsheet View Double click in cell A2 and change the coordinates of the corresponding point to (1, 2). After hitting the Enter-key, all points that depend on point A2 automatically adapt to this change, both in the Spreadsheet view as well as in the Graphics view.

Version 2: Change the initial points in the Graphics View Activate tool Move and drag point A2 to a different position in the coordinate system. Immediately, all dependent points dynamically adapt to these changes both in the Graphics View as well as in the Spreadsheet View.

Note: In order to restrict the coordinates of the points to be integers, you can change the option Point Capturing in the Options Menu to On (Grid). You can display the coordinate grid by selecting Grid from the View Menu.

Investigating Number Patterns

Let’s investigate how the surface of a cube changes depending on the length of its edges.

Preparations with Paper and Pencil

Calculate the surface of a cube for the given length e of its edges. Pick at least two edge lengths from each table but do not pick the same numbers as your neighbor.

Edge surface.PNG

Preparations in GeoGebra

  • Open a new GeoGebra window.
  • Switch to Perspectives – Spreadsheet & Graphics.
  • Show the Input Bar (View Menu).
  • In the Options Menu set the Labeling to New Points Only.

Construction Steps

Create a Scatter Plot from your Data

1 Enter the following numbers into the spreadsheet cells of column A: A1: 1 A2: 2
2 Highlight cells A1 and A2. Relative copy the values to cell A10 in order to create a sequence of different edge lengths.
Note Hint: This creates the integers from 1 to 10.
3 In column B, enter the surface values you calculated earlier next to the corresponding edge length of the cube.
Note Hint: You may collaborate with your neighbors to complete the table.
4 Select cell B1 and relative copy the formula down to cell B10.
5 Create a Scatter Plot:
  1. Use the mouse to highlight all cells of columns A and B that contain numbers.
  2. Right click (MacOS: Ctrl-click) on one of the highlighted cells and select Create List of Points from the appearing context menu.
Note: The values in column A determine the x-coordinates and the values in column B specify the y-coordinates of the plotted points.
Note Hint: The points created from the data are displayed in the Algebra View as a list of points. By default, GeoGebra calls this list L1.
6 Tool Move Graphics View.gif Use tool Move Graphics view in order to change the scale of the y-axis so that all points are visible in the Graphics view.
Note Hint: Select tool Move Graphics view. Click on the y-axis and drag it down until you can see the 600 tick mark.

Investigate the Number Pattern in Column B

7 In cell C2, enter the formula = B2-B1 to compute the difference of the two successive surface values.
Note Hint: After entering the equal sign, you can click on cell B2 in order to enter its name into the active cell C2.
8 Select cell C2 and relative copy the formula down to cell C10.
9 In cell D3, enter the formula = C3-C2 to compute the difference of the two successive differences.
10 Select cell D3 and relative copy the formula down to cell D10.

Task 1

Examine the number sequences in columns C and D. Make a conjecture about the polynomial function that runs through all points plotted in the Graphics view and allows you to compute the surface of a cube for any given edge length e.

  • Is it possible to determine the degree of this polynomial by investigating the sequences of differences you generated in columns C and D?
  • Explain to your neighbor why we were repeatedly calculating differences of successive values and what they actually mean.
  • Is it possible to determine the coefficient of the polynomial by investigating the sequences of differences you generated in columns C and D?
  • Would this also work if the values in column A are not successive integers (e.g. 1, 3, 5,…)? Give a reason for your answer.

Check your Conjecture about the Polynomial

11 Tool Slider.gif Create a slider n with Interval from 0 to 5 and Increment 1. Change the orientation of the slider from Horizontal to Vertical (Tab Slider).
12 Tool Slider.gif Create a slider a with Interval from 0 to 10 and Increment 1. Change the orientation of the slider from Horizontal to Vertical (Tab Slider).
13 Enter the polynomial f(x) = a * x^n in order to create a polynomial of degree n with coefficient a.
Note: Both the degree n as well as the coefficient a can be changed by using the corresponding sliders.
14 Tool Move.gif Change the values of sliders a and n to match your conjecture. Does the polynomial run through all points plotted in the Graphics view?

Enhance your Construction

15 Tool Insert Text.gif Select tool Insert Text and click on the Graphics view to open the text edit dialog window.
  1. Enter f(x) = into the text edit dialog window.
  2. Click on the graph of the polynomial to insert its name into the text edit dialog window.
    Note: GeoGebra will enter the syntax necessary for dynamic text automatically.
  3. Click on the OK button.
16 Tool Check Box to Show Hide Objects.gif Insert a checkbox that allows you to show/hide the polynomial’s equation.
Note Hint: Select tool Check Box to Show/Hide Objects and click on the Graphics view to open the checkbox dialog window.
  1. Enter the caption Show equation.
  2. Click on the little arrow to open list of available objects.
  3. Select text1 from this list and click the Apply button.
17 Tool Move.gif Activate the Move tool and try out if your checkbox controls the visibility of the text.
18 Open the Properties dialog and enhance the layout of the objects in the Graphics view (e.g. change the color of the polynomial and points, match the color of the text with the color of the polynomial, fix the position of the slider, checkbox and text in the Graphics view).

Task 2

  • Try if this concept of investigating sequences of differences of two successive function values works for all polynomials f(x) = a x^n.
Note Hint: You can enter a formula into cell B1 and relative copy it down to cell B10 in order to create a list of function values. Don’t forget to start the formula with an equal sign.
  • What modifications in the Spreadsheet view and Graphics view are necessary to be able to easily determine the constant of polynomials f(x) = a x^n + b?

Scatter Plot and Best Fit Line

Preparations

  • Open a new GeoGebra window.
  • Switch to Perspectives – Spreadsheet & Graphics.
  • Show the Input Bar (View Menu).
  • In the Options Menu set the Labeling to New Points Only.

Construction Steps

1 Enter the following numbers into the spreadsheet cells of column A:

A1: 1 A2: 5 A3: 2 A4: 8 A5: -2

2 Enter the following numbers into the spreadsheet cells of column B:

B1: -1 B2: 2 B3: 3 B4: 4 B5: 1

3 Create a Scatter Plot:
  1. Use the mouse to highlight all cells of columns A and B that contain numbers.
  2. Right click (MacOS: Ctrl-click) on one of the highlighted cells and select Create List of Points from the appearing context menu.
Note: The values in column A determine the x-coordinates and the values in column B specify the y-coordinates of the plotted points.
4 Tool Fit Line.gif Use tool Best Fit Line in order to create the function that best fits your data points. Highlight the cells and then click the tool.
Note Hint: Activate tool Best Fit Line and select all data points using a selection rectangle: Click in the upper left corner of the Graphics view. Hold the mouse key down while moving the pointer to the lower right corner of the Graphics view in order to specify the selection rectangle.
5 Change color and thickness of the line using the Properties dialog.
6 Tool Move.gif Using this construction you can easily demonstrate how outliers impact the best fit line of a data set: Drag one of the points with the mouse and explore how this modification influences the best fit line.
Note Hint: You can also change the initial data in the Spreadsheet view.

Importing Data from other Spreadsheets

Note: GeoGebra allows you to copy and paste data from other spreadsheet software into the GeoGebra spreadsheet
  • Select and copy the data you want to import (e.g. use the keyboard shortcut Ctrl-C in order to copy the data to your computer’s clipboard).
  • Open a GeoGebra window and show the Spreadsheet view.
  • Click on the spreadsheet cell that should contain the first data value.
  • Paste the data from your computer’s clipboard into GeoGebra’s Spreadsheet view (e.g. use the shortcut Ctrl-V (MacOS: Cmd-V) or right click (MacOS: Ctrl-click) on the highlighted cell and select Paste).

Challenge of the Day: Explore Basic Statistics Commands

Yesterday, you gave a mathematics quiz to the 25 students of your 1st period math class. After the quiz, you asked your students to rate the difficulty of the quiz on a scale from 1 ("very easy") to 5 ("very difficult").

  • 4 of your students rated the quiz "very easy" (1)
  • 6 students rated the quiz "easy" (2)
  • 6 other students rated the quiz "difficult" (4)
  • 1 student rated the quiz "very difficult" (5)
  • The rest of the students thought the difficulty of the quiz was "ok" (3).

Task 1: Create a histogram

Enter the data into GeoGebra’s spreadsheet and create a histogram.

  • Use the One Variable Analysis Tool in order to create a histogram.
  • Change the slider Classes in the appearing window to control the number of bars that are shown in your histogram.
  • Enhance the histogram by setting the classes manually and changing the start and width parameter.

Task 2: Determine mean and median

  1. Make a prediction for mean and median of the data you collected.
  2. Compare your solution by checking the left table of the One Variable Statistics window.
10 spreadsheet.PNG
© 2020 International GeoGebra Institute