digthedata

Q6 Spin Out

13. Dig the Data

Introduction

The Superb Slushie Company wants your help! You've discovered something very interesting; no matter what color your Slushie is, there is only one flavor. The store has an interesting advertising approach (which is why you go there): the customer spins a spinner when they order, and that determines the color of the Slushie they get. The owner happened to notice one day that he was running out of the Red color additive. He wondered if they needed to order more of the Red in his next order. You asked how many they sell in one day, and also in one week so that you can make a recommendation. You will do an experiment and graph the results before making your recommendation.


I Can Statements

  • analyze data and create different visual representations of it
  • can use spreadsheets, charts and visual representations as tools to help organize, evaluate, and present data

Key Vocabulary

Sum: The sum is the function of adding cells in a spreadsheet. (=sum)

Probability: Probability is a number between zero and one that shows how likely a certain event is to occur.

Play the Vocabulary Game below to practice the Key Vocabulary


You can change the Quiz mode to Match, Test, Learn, Flash Cards, Spell using the selection list at the bottom right of the activity that says "Choose a Study Mode". Direct link to the Quizlet.

Note:

  • GOOGLE Sheets users: If you are using Google Sheets stay on this page.

  • MICROSOFT Excel users please use the Spin Out With Excel menu on the right or the link provided here.

STEPS for Google Sheets users:

1. Start by copying this spreadsheet template to your own Drive space.

2. Work with a partner (or small group). If you are doing this on your own, be ready to take on different roles.

  • Spreadsheet Operator: Open the spreadsheet and save it with your name and spinner in the title ready to use. Be sure to share it with your group or partner.
  • Spinner Operator: Open a blank document (word processing, journal, or presentation program) to input screenshots and answers to the questions you explore as you go through this Spinner Project.
  • Analyst: Read the directions for each step and then reflect on the results and analyze the data.

3. The Spinner Operator will go to this interactive spinner to simulate the 5 Slushie colors used by the Superb Slushie Company. The Spreadsheet Operator will record the data into the spreadsheet template.

Note1: If you do not have a computer that can run this spinner, you could make a spinner by drawing a circle. it should be divided into five equal sections. Color the sections with: purple, yellow, blue, red, and Cyan (light blue).

Part 1 Collect data

1.1. Spinner Operator-

  • Look under the online spinner and set the Number of Sectors to 5 (one for each color).
  • The owner told you he sells an average of 10 Slushies each hour on the weekend.
  • Enter the number 10 in the Spins box.
  • Click on "spin" button to carry out this Experiment to see the results for Round 1.

Shows a spinner with 5 sections and number of spins = 10

  • Analyst: Look at the Column titled Theoretical %, why do you think they are all the same % at the start of this experiment?
    • Notice the Count column on the spinner shows how many times the spinner landed on a particular color sector.
    • It shows the Experimental % of your spin
    • It also shows the Theoretical % for a single spin of a single color (or 1 out of 5 chances to get any one color)
    • Are your results experimental %'s different than the theoretical ones?
    • Why do you think they are different or the same?

1.2 Spreadsheet Operator: Record the Round 1 results

  • Record the results for Round 1 in the Problem One section of the spreadsheet, (as called off by the spinner partner if working with one). An example is shown below.
  • Remember to RESET the spinner after each Round of 10 spins.
  • Continue to do Round 2 and Round 3, recording the results after your spins.

Screenshot of a spreadsheet with 5 color titles and numbers entered from the spinner experiment

  • Notice that the totals area automatically calculated for you. You will learn about formulas in the next part.

1.3 Problem 2.

  • The Slushie company owner told you that he sells between 700 to 1000 each week.
  • Repeat the experiment, but this time you choose the number of spins for each round to approximate his weekly sales (max is 1000).
  • Type the number of spins you have decided to use in the spreadsheet for Problem 2.
  • Set your spinner so that in 3 rounds of spins it will add up to your sales projection.
  • Record the data after each of the 3 rounds of spins. 

Reflection Questions:

  • Look at your results from 30 spins in Problem 1, and the results from your Problem 2. What differences do you notice?
  • What if you recommend if you only looked at your first set of spin data?
  • Did more spins change what you would recommend?
  • Make a list of things that can be misleading or wrong with data you see in ads on TV, in posters, or other locations. Is it misleading at times?

Continue to the Formulas section below.

 

Part 2 Formulas

Your next step is to use a formula so the spreadsheet will add up the totals for you in the Totals row in Round 2. You can do this by using the SUM function below.

Example: The formula used for Problem 1 in cell C8 to add Cells C5 + C6 + C7
Shows the formula =sum(C5:C7)

2.1 In the spreadsheet, in Problem 2, put your cursor in the Totals Row under the Blue column (cell C20 in the example used).

2.2 Using the spreadsheet provided for this activity, type this formula into the cell    =sum(C17:C19)  
Notice the Google helper sometimes suggests a formula for you.

  • The = sign lets the spreadsheet know you want to enter a formula.
  • The word sum will add up the cells you indicate
  • The : (colon) between the cells indicates to add up everything between the first cell you select (C17), and the last cell (C19) selected.

Press enter and if you did it correctly you will see the total. Google sheets also will let you know if you made an error and try to suggest a correct formula.

  • When you are done, put your cursor back in the cell and see the formula appear on the formula bar above the spreadsheet.

Screenshot of formula bar in Google sheets showing Fx and =sum(C17:C19)


2.3 POWER SHORTCUT: Filling the formula across to other cells in the row.

  • Once you have successfully entered the formula, select the cell once more. Look closely for the large square in the lower right corner of the cell.

Shows a larger black square in the lower right-hand corner of the cell.

  • When you put your cursor carefully over the corner square (it's called a fill handle) your mouse will become a large cross hair, click with your mouse and DRAG across to the right to the last column to total and let up on the mouse. It will fill the sum formula across to the others.

2.4 Calculating the Experimental % figures for your data

  • For both of the Problem 1 and Problem 2 data sets there is a row for calculating the Experimental % of times the spinner landed on each color. You will be able to compare these figures with the Theoretical %.
  • The challenge is to see how close or different your data is.
  • Put your cursor in the Problem 1 Experimental % cell under Blue (cell C11 in our example).
  • To calculate this, you are going to create a formula to divide the total number of spins for each color by the total number of spins done in all three rounds (30).  Try this formula   =C8/30 (in cell C11).
  • Then use the fill handle technique and drag it across the other cells! How close are your Experimental percents to the Theoretical ones?

Challenge:

  • Create a similar formula for Problem 2, but this time you won't be dividing by 30, divide by the total number of spins you did for all three rounds.

Continue to Part 3, Charts and Graphs

Part 3

This is a fun part but watch carefully and be ready to experiment. We will use the Google Explore feature to open graphing options after we select the cells to graph.

Screenshot of the Google explore feature, a gray square like a speaker bubble with a white 4-pointed star inside.

  1. Start by copying the color section names and paste them into the cells below the Totals for Problem 1 so that it looks like this. The purpose is to put the labels right below the data for the chart we will create.
    Looks like this now: See the color names now not only above the data, but also below the totals.
     
  2. Put your cursor in the total for Blue and drag across and down so that you have highlighted all the totals and names of the colors on the spinner (Cells C8 through G9 in our example)
    Screenshot of Selecting cells to graph C8 to G9
  3. Then use the Insert menu>Chart.
    Yes you could also use the Explore button (bottom right of the spreadsheet page) and scroll down through the different graph options (line, bar, circle)
  4. Select the pie chart, and if you scroll down you will see a 3D version.
  5. Close the Explore window using the X
  6. Select one of the slices (double-click) and on the Pie Slice customize menu change the color to the appropriate color for the data it represents. You only need a single click to select each sector to change their colors. Do this for each slice.
  7. On the right Chart editor menu, select Pie chart if it isn't already opened down and select Slide Label. It will be on None, but change it to Percentage.
  8. Next select Legend in the Editor window and change the Position that says Auto to None. This cleans up look of the chart.
  9. Close the customize window.
  10. Click on the chart and notice the three gray dots in the top right corner of your chart and notice the options
  11. Select Copy Chart
  12. Go to the tab titled Recommendation and paste (right-click copy or ctrl-v)
  13. resize it so that it fits into the large square provided
  14. Type in a recommendation based on your data.
  15. Return to the Experiment sheet and select the chart, use the 3 dots and delete the chart.
  16. You will do the same for Problem 2, but this time select the cells, and use the Insert>Chart menu
  17. Double-Click on each sector to change the colors to match your data again.
  18. Change the Labels to show Percentage (it takes some searching)
  19. Copy the chart and paste it on the Recommendation sheet page
  20. Resize it to fit
  21. Write your recommendations to the Slushie Company based on this data, and explain why this might be different from your first recommendation.

Whew! Congratulations with creating your charts!

 

Part 4 Advanced Probability (Optional)

One of your friends challenged you with a tricky question, "How many times would you have to order to get one of each color?"

You decided to figure it out!

Resources:

What is the probability of spinning all of ONE color 5 times in a row?

What would the probability be to spin a random color 5 times in a row?

You may find using a probability tree diagram is very helpful. It would start something like shown below.

Resource: MathisFun.com Probability Tree Diagrams

BGROY

BGRYO

BGYRO

BGYOR

BGORY

BGOYR

 

BYGRO

BYGOR

BYRGO

BYROG

BYOGR

BYORG

and so on for the other branches in the probability tree. There are 6x5 possibilities. What is the probability of spinning one of each color?

Think of a 5x5 Punnet Square with 5 dimensions instead of 2. Whew!

Completing this Quest

  • Save your spreadsheet and Spinner report document.
  • Prepare a short (no more than three minute) presentation about something you learned from your Spinner Project.
  • Identify what you want your audience to know at the end of your presentation.
  • You should use some of the screen shots you took for illustration in your presentation.
  • You might pose an interesting question or problem to your class in your presentation.
  • Try to engage your audience.
  • Check with your teacher and use multimedia resources you learned about in other quests. Make this a fun but educational Report Out.
     
  • Check with your teacher on how they want you to turn it in or share it with them.


Check off this Quest on the 21t4s roadmap 

   I have completed this Quest and I am ready to tackle Quest 7, Just FX


MITECS  Michigan Integrated Technology Competencies for Students, and

ISTE Standards for Students

5. Computational Thinker
b. Collect data or identify relevant data sets

6. Creative Communicator
c. Communicate complex ideas clearly and effectively by creating or using a variety of digital objects such as visualizations, models or simulations
d. Publish or present content that customizes the message and medium for a variety of audiences