# Year 11 - Maths Apps

Data Collection and Analysis

Smarties Analysis:

Once you have collected the data from your box of Smarties you will need to enter this on the computer.  You will do this using EXCEL.  Each colour is a category.  Set up your spreadsheet so that it looks similar to the following.  Note the formulas that are used.  If your spreadsheet is located differently you formulas will need to change.

The formulas that are used for this are shown below.  You will need to include your own formulas for the class data

You now need to create a bar chart to represent this data.  Create a chart for your own data and a chart for the combined class data.  To create the chart you will need to highlight the Colour category information and the frequency information.  Click on the Chart icon and choose the Column option.  See below.  Remember you must label your charts appropriately by completing the Chart Wizard.

For the Class Data you will need to choose rows that are not adjacent to each other.  To do this choose the first row and then with the Ctrl key pressed choose the other row.  Then follow the usual procedure to produce your chart.

Frequency Data:

Once you have completed the data entry and graphing as indicated above our next task is to use EXCEL to further analyse our data.  Our first task is to determine the frequency of a given number of M&M's of any colour occurring in a any box.  For example how many times do we find 6 M&M's of any colour occurring in a box, how many times do we find 10 M&M's of any colour occurring in a box.  As your data table contains well over 100 entries it would be tedious to sit down with a tally sheet and count the occurrences of 1, 2, 3...... 15 of any colour occurring in a box.   Imagine if you had 10000 cells to count. Fortunately EXCEL has a very useful function that can do the counting for us.  The function is the COUNTIF function.

You will notice that you can take the Number column down to a value representing the largest number of M&M's of any colour found in any box..  Click on the first cell in the Tally column.  From the Insert menu choose the Function option.

Then select the following options.

Click OK and the following dialog box should appear.

Click in the Range box and then drag you mouse across the tabulated data you want examined.  Don't include any of the totals.  Your dialog box might look something like the following.  Note that when you choose the criteria you should click on the cell containing the number "1" in the Number column.  Don't type in the number "1" rather there must be a cell reference.

The formula in the first cell of the Tally column will have a formula similar to that shown in the following,

Unfortunately it is not possible to copy this formula down the column unless we convert the range to an Absolute Reference.  To do this highlight the range part of your formula.  In the case above it is the B12:D19 section.  Your range will be different but the same principles apply.  Once highlighted press the F4 key.  This places a \$ mark in front of the row and column reference.

Now you are able to copy the formula down the column.  Note the Frequency and the Tally columns should contain the same data.  Write a formula in the top cell of the frequency column so that this cell will equal the top cell of the tally column.  Copy this formula down the column.

To generate data in the Cumulative Frequency column you will add the cells in the frequency column together.  This is done by writing an appropriate formula.  That is the value in the top cell of the frequency column is added to the cell immediately below it.  This value is then placed in the cumulative frequency column.

Look carefully at the following diagram.  You must write a formula in the cell with the circle that will be able to be copied down the column.  The formula to create the value "1" in the cumulative frequency column just equals the cell containing the "1" in the frequency column.  However the "1" in the cumulative frequency column must be added to the "3" in the frequency column to give you the value "4".  This pattern must be able to be repeated down the column.

Your next task is to create charts of Number vs Frequency and Number vs Cumulative Frequency.  Use the Scatter Graph point to point option.  The line graph of cumulative frequency results is called an Ogive.

Add two more columns to your data that will allow you to calculate, using appropriate formulas, Relative Frequency and Percentage Frequency.

When you have completed your work make sure that you let me know.  If I don't know the work is completed I will assume that it hasn't and record the appropriate mark.

Exercises:

Q4) The following frequency distribution table gives the number of days each weather type for the month of January.  Construct a Bar Chart to represent this data using Excel.  Ensure that the chart is labelled appropriately.  The table below should copy and paste into an Excel spreadsheet
 Weather Type Hot Warm Mild Cool Frequency 8 15 5 3

Q5) An international cricket player keeps a record of the way in which he has been dismissed when batting.  Represent the following information using an Excel column graph.  Ensure that the chart is labelled appropriately
 Type of Dismissal Caught Bowled LBW Run Out Frequency 35 40 15 7

Q6) Among other questions, a survey asked for the level of education of each participant.  The data were organised into the following categories.
 X Left before Yr10 Y10 Year 10 Y12 Year 12 T Tech College Diploma U University Degree

The data is shown below.
 Y10 Y12 Y12 U U T Y12 Y10 U T Y12 Y12 Y12 U T T Y10 Y12 U T U Y10 T X U T U Y12 Y12 T Y12 Y12 T U Y10 T Y12 Y12 X Y12 Y10 Y12 Y12 U U T Y12 Y10 U T U Y10 T X U T U Y12 Y12 T Y10 Y12 Y12 U U T Y12 Y10 U T Y12 Y12 Y12 U T T Y10 Y12 U T U Y10 T X U T U Y12 Y12 T Y10 Y12 Y12 U U T Y12 Y10 U T

a)  Using the countif  function construct a frequency distribution table of the form,

 Category Frequency X Y10 Y12 T U

To count each category insert the appropriate formula into the appropriate frequency cell.  An example of the approach to take is shown below.

a) Construct a bar chart using this frequency information.

b) Determine how many participants achieved an education to Year 12 level?  .Use appropriate Excel formulas

c) How many participants achieved an education after year 12 level?  Use appropriate Excel formulas

d) What is the percentage frequency of the students who achieved an education after year 12?  Use appropriate Excel formulas

e) What type of data does this represent.

Working with Numerical Data:

When there is a large amount of data or if the data are spread over a wide range it is useful to group the data into groups or classes.   The following table provides an example of grouped data.  It shows the number of passengers on each of 20 bus trips

 Number of Passengers Frequency 5 - 9 1 10 - 14 6 15 - 19 8 20 - 24 4 25 - 29 1

In choosing the group size it is important to choose a group size so that 5 - 10 groups are formed.  If too few groups are formed then trends and patterns may not be obvious and when too many groups are formed any pattern or trend may again not be obvious.

In the following exercise you will use the frequency function in Excel to help you organise data into group.  This will avoid having to tally the values by hand.

The following data represents the number of nails in each of a sample of 40 boxes.

 130 122 118 139 126 128 119 124 122 123 132 138 129 139 116 123 126 128 131 142 137 134 126 129 127 118 130 132 134 132 137 124 134 134 120 137 141 118 125 129

The groups that we will choose are as follows,

 No of Nails Frequency 0 - 114 115 - 119 120 - 124 125 - 129 130 - 134 135 - 139 140 - 144 145+

If you were doing this exercise without a spreadsheet you would need a Tally column.  However we are going to make Excel do the counting for us.  To do this you will need to copy the table of nails in each sample to an Excel spreadsheet.  Your spreadsheet should look similar to the following,

You will now need to copy the No. of Nails group column from the table above to your spreadsheet.  Your spreadsheet should now look similar to the following .

In the column next to the No of Nails column type the maximum values for each group.  See the diagram below.  Note how we have titles this BinLimits.  This is the name given to this type of data by Excel.

Into the cell next to 114 insert the frequency function.  To do this choose Function from the Insert menu.  See below.

When the Paste Function dialog box appears choose the Frequency function as shown below.

From this the Frequency wizard dialog box appears.  The Data_array  is your data table and is selected by dragging the mouse from the top left of the table to the bottom right.  The Bins_array is selected by dragging the mouse over the BinLimits column.  See the diagram below.

After you press the OK button nothing appears to occur.  See below,

To show the frequency of each range of values you need to proceed as follows.  Click of the cell that is equivalent to C10 on your spreadsheet.  Now drag the mouse down the column.  DO NOT drag via the lower right dragging point.  You do not want to copy this formula.  Rather what you are doing is defining space for the formula to fully express itself.

Now highlight the top formula bar.  See below.

Once this is highlighted press the following keys simultaneously Shift Ctrl Enter.  Now you should see the frequencies of the various ranges appear.  See below.

Label this column Frequency.  Create a frequency column graph with frequency on the vertical axis and the various groupings on the horizontal axis.

When you have completed this exercise make sure that you let me know so that it can be marked off.

Mean, Median Mode

Definitions:

Mean: The MEAN or AVERAGE is the SUM of all the scores divided by the number of scores.

Median: The MEDIAN for a set of data is the middle score, when the data has been arranged in order from lowest to highest.

Mode: The MODE for a set of data is the score that occurs most often.

The members of a basketball team would like to compare the number of points that they have scored (over 12 games) with each other and with the number of points the team has scored.

The number of points each player has scored is as shown in the following table.

 Amanda 5 8 2 10 6 8 4 4 6 10 Colin 4 8 6 7 7 9 10 6 5 8 Eloise 12 8 8 16 4 5 7 10 11 8 Diana 4 3 5 2 6 8 4 3 3 6 Tran 10 14 7 8 12 16 10 10 8 6 Michelle 12 4 6 10 9 9 5 16 8 12 Shaheid 4 7 6 8 7 5 3 8 9 2 Greg 14 12 8 7 10 9 5 12 15 8

Copy the data from this table into Excel.  If necessary insert a row above the top row.  Type in the following column headers in order from left to right.

Name, Game 1, Game 2 ............... Game 10, Total, Average.

In the cell below Greg type Total, and below this cell type Average.

Your spreadsheet should now look ,in part, like the following (the yellow background may or may not be present depending on whether you merely copied and pasted the data or whether you retyped the data into Excel),

Determining totals and averages is very easy using a spreadsheet program.  To find a total we use the sum function and to find an average or mean we use the average function.  To calculate the mode use the Mode function and  the median use the Median function.  To determine the range you will need to subtract the lowest value in the list from the highest value.  To do this you will need to use the minimum function, Min and the maximum function, Max.  Remember all functions must be preceded by an "equals" sign.  The minimum function will find the lowest value in a list and conversely the maximum function will find the highest value in a list.

To find the total and average values for column B firstly type an equals sign into cell B10, or the equivalent cell on your spreadsheet. Now type the word Sum( after the equals sign.  Note the position of the (   Click the mouse on cell B2 (or equivalent) and drag down to cell B9.  Type a  and then press Enter or Return.  The sum or total of this column now appears.  Into cell B11 find the average by repeating all of the above but rather than typing Sum type the word Average.  The formulas can also be pasted from the menu Insert function.  For the function to act as a function you must have the parentheses after the function name.  That is, the function can be typed as  =sum(  or if it is pasted a dialog box will appear asking you for the cells you wish the formula to apply to.  Type in functions to find the median, mode and range as shown below.

Highlight cells B10 to B14  Copy them and then paste them across to column K as shown in the following diagram.

Into cell L1 type the word Total and into M1 type the word Average.  Using the technique as outlined above add formulas to find the totals and averages for each row.

Write appropriate formulas in cells L2 to L9 to calculate the total for each row.  Remember you can type a formula in the cell L2 and then copy it down the column.  Repeat the procedure in column M but this time calculate the average using the Average function.  Into columns continue your analysis by typing headings as shown below,

Add the appropriate formulas to cells in columns N, O and P and calculate these statistical quantities for each player rather than each game as you did previously.

Measures of the Spread of Data:

We have discussed in class how there are three ways to measure the spread of data.   These are

a) Range
b) Quartiles
c) Standard Deviation

Remember the spread of data is a very important concept and has a major influence on the way your unit scores are calculated in years 11 and 12.

In the following activities you will design Excel spreadsheets to answer a number of questions. These activities will help you with the assignment.

Activity 1: You MUST use spreadsheet formulas to do these calculations.  All necessary answers are to be included on the spreadsheet.  Make sure that you save all of your exercises to your home folder.

Look at the following set of scores.
 7 6 8 10 9 8 5 9 10

Copy these to an Excel spreadsheet and using appropriate formulas calculate the mean, median and standard deviation.  There are two Excel formulas to calculate the Standard Deviation.  The first is STDEVP and the other is STDEV.  The STDEVP is the formula that is used to find the standard deviation of an entire population.  The STDEV is the form used when you take a sample of a population.  If the population is very large and the sample size is very large then these two values given by these two formulas should be very similar.  Generally we will always use STDEVP because we are finding the SD of the entire population.

The data above can be placed in a single row or a single column.  A column is more convenient.  The order of the numbers in the column does not matter.   Insert the appropriate formulas in the cells and calculate both forms of the standard deviation.   See below

Excel can also be used to find Quartiles.  Remember Quartiles are derived from cumulative frequency polygons, or as we have called them Ogives.  The Excel function to find these values is =QUARTILE.  In an Ogive you are able to include a Percentile axis.  The 50th percentile is then a point where we have 50% of the data above this value and 50% below this value.  Such a value is termed the median.  This is sometimes written as Q2.The lower quartile, written as Q1 or QL , represents the 25th percentile.  Below this particular value 25% of the data can be found.   The upper quartile is written as Q3 or  QUIt is the value below which 75% of the data can be found and above which 25% of the data can be found.  Modify you spreadsheet as shown below.

You will notice that the minimum, Q1, Q2(median), Q3 and the maximum can all be calculated using this formula.  Make sure that you use this function to calculate each of these values.  Also calculate the interquartile range.   This is the range of data from the 25 percentile to the 75 percentile.

The Quartile function works firstly by defining a range of cells, the array, and then by selecting the appropriate value for the Quart.   See below

After you have inserted the formula in cell B12, or the equivalent on your spreadsheet, you must convert the range to an absolute reference before it can be copied down the column.   To do this look carefully at the following diagram

Here the reference B1:B9 is highlighted and then the F4 key is pressed.  If the F4 key does not function then you will need to type \$ signs as shown.  The \$ signs turn a relative reference to an absolute reference.  The formula can now be copied down the column.  Of course the value of the Quart  will need to be altered as appropriate in each case.

Further activities on Standard Deviation:

Copy the scores to an adjacent column on your spreadsheet.  Do this in such a way that 5 is added to each value.  For example the formula that you write could be = A1+5  Find the new mean and standard deviation of the scores.

Copy the original scores to another column.  Do this in such a way that each score is multiplied by 2.  Calculate the new mean and SD.

Write a brief note as to the effect of multiplying by 2 has had on the mean and SD.

Copy the original set of data to another column.  In this exercise we will use Excel to calculate the SD by using the formula shown below.  Using Excel makes this formula easy to use.  To do this you will firstly need to create two columns just as you would on paper.  The columns are ,

and

The x bar refers to the mean or average of a list of scores.  The x value refers to a particular score.  You will then need to sum the squared column  then divide it by the number of scores and then take the square root of the number.  Remember Excel formulas must be used throughout.  Ensure that as fewer numbers as possible are used in formulas.  Use references to the cells containing the data.  Not the data itself.

The formula that is used  to calculate the SD is as follows

Which Excel formula gives you this particular answer, STDEVP or STDEV

Box and Whisker Plots - Box Plots

In the activities that you completed previously you used the Excel function QUARTILE.  Using this function appropriately allowed you to calculate the minimum value, first quartile, median, third quartile and the maximum value.  However apart from using the graphics calculator you were not able to generate Box-and-Whisker Plots.  In the following activity you will be able to create these plots that show the variability contained within a set of data.

The key features of the box-and-whisker plot are shown in the following diagram.  The five values shown in the diagram are sometimes referred to as the five-figure summary for a data set.

The site that we will be using is shown in the diagram below.  Click on the diagram to visit this site's homepage.

The activity that we will be looking at today looks uses the Java applet shown below.

This applet allows you to create box and whisker plots very easily.  It also allows you to examine the effects of outliers - see later exercise.  The Q1, Q2( median) and Q3 values are also determined.

### Worked Example 16:

Draw a box-and-whisker plot for this data set: 2, 2, 3, 4, 5, 5, 5, 5, 6, 7, 9, 12.

This question is from your text.  The box plot shown in the text is shown below.  Your task is to use the Box and Whisker Java applet to generate a similar box and whisker plot.

To draw the box plot for this set of data click here  From the drop down box that appears choose the My Data option.  See below.

You will also need to clear the data from the bottom data box.  See below,

Enter the data shown above.  You need to place the data in column format.  After pressing the Update Boxplot button you should see the following,

Check the median, the lower quartile and the upper quartile with the one shown above.

### Example 18

Draw a box-and-whisker plot for the given data set using the same Java applet .

 Stem Leaf 3 0 3 6 4 1 3 5 7 9 5 4 5 7 8 6 2 4 7 9 9 7 0 0 0 1 4 7 8 8 3 5 7 8 9 0 0 0 0 0 1

Key: 3|4 = 34

With this data you will need to check the key firstly to ensure that the data is entered as it is meant to be.  Note that there are multiple identical entries.  Each of these must be entered.

Outliers:

Using the box-plot applet generate a box-plot for the following data,

1, 1, 14, 16, 20, 24, 24, 27, 29, 30, 105.

You will notice that in this set of data there is a value of 105.  The box-plot that you have generated has a very long whisker.  This value that is removed from the other data is called an Outlier.   The rule to decide if data can be considered as an Outlier or not is stated as follows

An OUTLIER is said to be a value which lies more than 1.5 x IQR away
from the nearer of the upper and lower quartiles.

The term IQR is the abbreviation for Interquartile Range.

If you set the sliding scale to 1.5 and check  the box, see below

The the box-plot that you see will be plotted according to the rule stated below,

When we draw a box-and-whisker plot for a data set which contains outliers we make a slight adjustment to the way the whiskers are drawn.  The whisker is drawn only to the last data value which is not an outlier and we then use a * to represent each outlier.  The following screen dump from a graphics calculator shows that modification.

Exercises: The following exercises come from Exercise 11 of your text.  Complete them using Excel and/or the Box-Plot Applet.  To find the five figure summary data use the Quartile function in Excel.  Click here to review the use of this function.

Exercise 5: The number of sales made each day by a salesperson is recorded over a 2-week period:

25, 31, 28, 43, 37, 43, 22, 45, 48, 33

a)      Prepare a five number summary of the data. Draw a stem-and leaf plot of the data.   To do this we will use a Stem and Leaf JAve applet.  Click on the following link to activate this applet.  You should see the following,

b)      Draw a box-plot of the data

Exercise 6Using Excel and the Java applets, find the five-figure summary for each of the following data sets.  Note this data can be copied and pasted directly into an Excel spreadsheet

a)      1, 4, 6, 3, 2, 4, 5,   7,   8,   9, 10,   2,   5,   6,   7,   4,   5,   3,   5,    1
7, 9,  6,   9,   2,   4,   8, 10, 11, 15, 17, 19, 13, 12, 14, 16, 17

b)      8, 12, 15, 18, 23, 34, 32, 12, 16, 13, 18, 19, 13, 15, 21,
32, 23, 34, 23, 12, 18, 19, 17, 1 5, 13, 17, 19, 20, 32

 c) Score 8 9 10 1 12 13 14 Frequency 10 12 1 11 5 7 9

 d) Score 23 24 25 26 27 28 29 Frequency 8 11 14 12 13 7 3

Exercise 8:  For each of the following data sets draw a box-and-whisker plot which clearly identifies any Outliers.  Remember that you need to restrict the Extreme Range to 1.5 and make sure that you check the Exclude Extreme Outliers in Graph box.   Note these data sets can be also copied and pasted directly into an Excel spreadsheet.  The data can be pasted into the Java applets but will require some manipulation as the applets require a column format, i.e., only one data set per line.

(a) 22, 24, 25, 19, 45, 73, 21, 25, 18, 16, 22, 25, 28, 19, 17, 16, 23

(b) 101, 108, 113, 121, 113, 67, 97, 135, 141, 123, 116, 119, 121, 123, 134, 132, 119, 118

(c) 38, 38, 37, 39, 41, 42, 42, 45, 43, 44, 41, 42, 46, 18, 42, 45, 47, 72, 32, 43, 41, 40, 42, 45

(d) 113, 134, 123, 143, 123, 129, 127, 137, 136, 139, 129, 130, 140, 76, 72, 112, 142, 132, 141, 132, 137

Distribution Shapes and Mean, Median and Mode:

The following diagrams show where the mean, median and mode are located on different shaped distributions

Our next investigation will involve the use of a Java applet that allows you to observe the effect of an alteration in standard deviation and the median on a distribution.  The applet appears as shown in the following diagram.  Click here or on this diagram to open the applet.

The Graph shows a normal distribution. The mean is indicated by a blue line, the median by a light blue line, and the standard deviation is indicated by a red line on either side of the mean. The black circles at the bottom of the red lines and the light blue line can be dragged with the mouse to change the standard deviation and the median. Note that the blue line will not be visible if the light blue line is moved to the same place. The red graphic in the upper-left corner gives the scale of the grid.

You can also set the standard deviation by typing a new value in the box marked New Standard Deviation and clicking the Set button. The current standard deviation is printed in the label just below the graph. The median can be changed the same way, with the New Median box.

To draw a histogram, choose the number of trials you want from the choices in the pull-down menu and click the Create New Histogram button. You can change the bin size using the scrollbar.

The first activity is to ensure the light blue line (median) and the dark blue line (mean) are superimposed.  Set the standard deviation to 0.4.  Drag the black dot beneath the red line towards the centre.  Note the effect on the standard deviation.  Now drag the black dot on the red line back the other way.  Observe the effect on the standard deviation.

Although you cannot change the mean you are able to change the median,  The median is the light blue line.  The black dot at the base of this line can be dragged one way or the other.  Observe the effect of the median being less and greater then the mean.  Compare to the histograms shown above.

Exercise 12:

Q2) Open an Excel spreadsheet.  Look carefully at the following data and set up the spreadsheet to calculate the information as indicated in a to e below .   Remember you must use standard Excel functions for this exercise.  Answer any questions on your spreadsheet.  Remember to save your work.

Consider the following two groups of people.  Copy the diagram to Excel and

(a)     Calculate the mean height, median height and mode height for each group. What do you notice?
(b)     Are the groups really the same?
(c)     Which group would you expect to show the greatest range in heights?
(d)     Which group would you expect to show the greatest interquartile range in heights?
(e)     Which group would you expect to show the greatest standard deviation in heights?
(f)      Calculate these statistics to confirm your predictions.

Q4)   Look carefully at the following data.  Your task is to transfer this information to an Excel spreadsheet so that you are able to calculate the various statistics indicated.  To assist with task set up three columns names as follows, Life(h), Frequency, fx an (x - Mean) and an (x - Mean)2 . Write an appropriate formula to calculate the fx column, the (x - Mean) and the  (x - Mean)2 columns.  As you are dealing with grouped data you will need to enter values for Life(h) which are midpoints in each group shown.  For instance for the first set of data the midpoint is 250 plus 200 all divided by 2.  Remember that the S symbol means the sum of.  To calculate the mean use the formula,

To calculate the population standard  deviation for part (b) use the following formula,

For part (c) use the following formula

The Varience is closely related to the standard deviation.  Use the formula,

to calculate this.

The following frequency distribution table shows the life expectancy of 175 household light globes.

 Life (h) 200- 250- 300- 350- 400- 450- 500- 550- 600- 650-<700 Frequency 2 5 12 25 42 38 26 15 7 3

(a)  Find the range of the data.
(b)  Find the mean and standard deviation in the lifetimes of these light globes. (use population sd)
(c)  Estimate the mean and standard deviation in the lifetimes of all light globes of this brand.  Use the sample SD formula.
(d)  Find the variance in the lifetimes of these light globes.
(e)  Estimate the variance in the lifetimes of all light globes of this brand.

Z  - Scores and Scaled Scores:

The z-score of an observation measures the number of standard deviations the particular observation (or raw score) lies above or below the mean. It can be found using the following formula.

z-scores are sometimes referred to as standardised scores.   A score above the mean will have a positive z-score and a score below the mean will have a negative z-score.

Use an Excel spreadsheet to work through the following problem,

Example 1:Lucianno scored 78% in his first Maths test and 58% in the second test.

 Mean Standard Deviation Test 1 64 10 Test 2 48 7

(a) Use the table to calculate z-scores for each test.
(b) Which result was better?

Example 2: Calculate the scaled score of a student who had a z-score of 1.5 in Mathematics Applications which is scaled to a mean of 65 and a standard deviation of 12.

Bivariate Statistics - The Pendulum:

A simple pendulum consists of a mass hanging from the end of a string.  The string is considered to be of negligible mass in comparison to the system as a whole.  It was stated that the period  (T) of the pendulum can be described by the formula,

The period is the time for one complete swing.  That is the time it takes to return to your hand after it is released.  l is the length of the swing and g is the acceleration due to the Earth's gravity.

Your data was plotted using the graphics calculator   Up until now our examination of statistics has considered only one characteristic of the data at any one time.  For example we may determine the mean or standard deviation of such data. Such data is called Univariate Data.

With Bivariate Data we examine two sets of data simultaneously and a relationship between the variables may be observed.

Our Practical So Far:  We have collected data for the period of the swing and the length of the pendulum.  We identified an independent variable - the one under our control - the length  and a dependent variable - the one that depends on the length - the period of the swing.  To plot this data using Excel we use a Scatter Plot.

The data collected by various groups is listed below.  Copy and paste two groups of data to Excel.  Make sure one set of data is the one that your group collected.  If it is not listed here you will need to type it into Excel.

 Adam's Group Period (x 10) (s) Length (m) 25.46 1.95 23.12 1.36 21.04 1.18 17.65 0.77 15.54 0.63 Sarah's (x 10) Group 23.19 1.26 21.62 1.06 20.19 0.86 17.43 0.66 15.03 0.46 Axel's (x 10) Group 31.91 2.57 29.85 2.37 27.07 2.17 25.13 1.97 23.78 1.77

Set up a spreadsheet that will enable you to do the following.  Use two set of data for this exercise.  The data that you collected should be included as one of the two sets of data to be analysed.  Remember you cannot combine the data into a single data set. The data for each group must be dealt with separately and graphed separately

a) Calculate the time for one swing,
b) Calculate T2 (period squared),
c) Allow you to calculate a value of the acceleration due to gravity for each length of pendulum using the equation.

d) Generate scatter plots for T vs l and  T2  vs  l.  You must also add a trend line and an equation  for each line as well ans the correlation coefficient R2.

An example of how to set up your spreadsheet is shown below.

Notes:  To graph this data you must arrange it in numerical order based on the length of the pendulum.  That is you must arrange the tables such that they the lowest value for the length is listed first.  To arrange data use the Sort option from the Data menu.

To use Pi(p ) in an Excel formula it must be entered as =Pi() You must include the empty parentheses.  Use parentheses appropriately so that you get the correct order of operations.  The value of g that you calculate should be close to 9.81.  It has units metres/second/second (m/s/s)   This means that any object dropped on Earth will accelerate towards the Earth's surface in such a fashion that it increases its speed (velocity) by 9.81 m/s every second.  This means that after 2 seconds it is travelling 2 x 9.81 m/s/s, i.e., 19.62 m/s/s and after 3 seconds it is travelling at 3 x 9.81 m/s/s, i.e., 24.93 m/s/s.

Of course objects as they fall are always severely affected by the air they fall through.  Air resistance, as it is called, effectively means that you are not able to determine the acceleration due to gravity by timing the fall of an object.  Most object reach a terminal velocity very quickly.

Add Trendlines  to both graphs.  Make sure that you set the options as shown below.  Also you will need to force the line to be extended, extrapolated back to x = 0, that is set the Forecast backwards by an appropriate number of units.

Question 1:  Which graph length vs period or length vs period squared seems to follow a straight line relationship more closely.  This is measured by the correlation coefficient R2 The closer this value is to 1 the better the sets of data fits the straight line relationship that we have derived by adding the trendline.  What effect does forcing the line through the origin have?  Is it realistic to to this?  That is, is it reasonable to say that a pendulum of zero length has a zero period of swing.

Modelling Correct Period:
Copy one set of data  to another convenient part of the spreadsheet   For  this part of the activity you will adjust through a process of trial and error the value of the time for 10 swings so that a value of 9.81 (approximately) appears in the g cell.

Modelling Correct Length:
Copy the original set of data  again to another convenient part of the spreadsheet.     Carefully alter the length measurement until the value of the g value is 9.81 m/s/s.

Question 2: Do you notice any pattern as you adjust the period or the length?  Can you decide which area of measurement included the greatest uncertainty?

Further Investigations:

To investigate some other aspects of a pendulum's motion we can view some web-based simulations.  Click on the following link, http://www.walter-fendt.de/ph11e/pendulum.htm Here you should see a Java based simulation.  Accept the default values as shown.

Read the introduction carefully then carry out the following investigations.  Make sure that you return the settings to the default values between each activity.  To do this press the Refresh icon on the toolbar or press F5

Q3) Double then triple the mass.  What happens to the oscillation period?

Q4) Double then triple the length.  What happens to the oscillation period?

Q5) Double then triple the amplitude.  What happens to the oscillation period?  Summarise how the mass hanging from the pendulum, the length of the pendulum and the amplitude affect the oscillation period of the pendulum.

Q6) The gravitational field on the Moon is  1.62m/s/s  and on Jupiter it is 23.54 m/s/s.  What happens to the period of oscillation a pendulum of equal mass and equal length placed on the surface of these two bodies?

Q7) The graph shown above of displacement vs time generates a graph that we have looked at previously.  You will note how the graph repeats itself every 4.49 seconds.  What type of graph is shown here?

Q8) Vary the length of the pendulum in 1 metre increments from 1 metre to 9 metres inclusive.  Record the oscillation period.  Transfer this data to a spreadsheet.  Add a period squared column.  Look carefully at this data and answer the following questions.
a) If the period is doubled how must the length of the pendulum have changed?  What happens if the period is tripled?
b) If the period squared value is doubled how must the length of the pendulum have changed? What happens if the period squared value is tripled?
c) Which relationship is linear length and period or length and period squared.

Assessment: To be marked off as having completed this work please ensure that I see what you have done and that the work is printed and submitted for checking.