> ?> ^@\p
STAFFCOOMBESS Ba==[;%8X@"1Arial1Arial1Arial1Arial1Arial1Arial1Arial1Arial1Arial1Arial""#,##0;\""#,##0""#,##0;[Red]\""#,##0""#,##0.00;\""#,##0.00#""#,##0.00;[Red]\""#,##0.005*0_""* #,##0_;\""* #,##0_;_""* ""_;_@_,)'_* #,##0_;\* #,##0_;_* ""_;_@_=,8_""* #,##0.00_;\""* #,##0.00_;_""* ""??_;_@_4+/_* #,##0.00_;\* #,##0.00_;_* ""??_;_@_ + ) , * # `Initial preparation"Sampling,Further investigation.
StatisticsL4 Box plotsEHNow you try it.,`iZR3 @@
[rThin the data down. Remove all the unnecessary fields. Keep info you might need later, e.g. Gender and year group.5Remember years 79 are on a separate sheet from 1011To remove a field select the column, by clicking on its letter, e.g. click on G, then use Edit > Delete. This will remove the column entirely.<You should end up with a reduced spreadsheet something like:
Year GroupGender
Height (m)Weight (kg)MaleFemale.bIt is probably worthwhile putting the year 79 data at the bottom so it is all on one spreadsheet.WOnce numbers are decided, lets say I need 10 year 11 boys, then they can be picked out.To ease counting it is probably a good idea to sort the data. This must be done with care, otherwise matching bits of data get separated.It is best to select entire rows, using the numbers on the left, to first select the data, include the row with the column hreadings.6The data is now sorted years 7  11 and female  male.LIt would be useful to count how many there are in each of the 10 catagories.They can either be picked randomly, using calculator or computer random number generator, or systemmatically, every 7th (or whatever the right number is)7Mark the picked ones with a * in the next empty column.@This can be done across the board fro years 711 and male&female*When complete select all the data again, selecting whole rows again, and sort on the column with the *s in. This will collect all the sample data together.eYou can now do some initial comparisons. Like looking for the relationship between height and weight.zNote when drawing graphs in excel XY scatter graph is the only useful one. Do not use Line graph as it isn't a line graph!MOnce this is done there are some tools which excel has which could be useful.These operations are detailed in the following sheets. You MUST have planned to do these things, in your plan, and justified why you are doing themKAlso of course they must be referred to in you analysis, what do they show.CIt is possible to calculate mean, median, quartiles using formulae.sWith some work it is also possible to draw box plots to compare data (although you might prefer to do them by hand)rBox plot analysis needs to be very detailed, with specific numerical analysis, not just boys are taller than girlsXTo delve further into the data it needs splitting up, one field should now be the focus.=AVERAGE(C6:C20)The mean average=MEDIAN(C6:C20)
The median=QUARTILE(C6:C20,1)The lower quartile (quartile 1)=QUARTILE(C6:C20,2)The median (quartile 2)The upper quartile (median 3)=QUARTILE(C6:C20,3)=MIN(C6:C20) The smallest number in the range=MAX(C6:C20)The largest number in the range=SUM(C6:C20)The sum of the data'Numerical statistical formulae in excel4Excel doesn't draw box plots but you can create one.oYou need to draw an xy scatter graph which traces around the outside of the plot, a kind of dottodot diagram.xIf you can't do this it doesn't matter, do them on graph paper, by hand next to each other, you will not lose any marks.MinLQUQMax,I'll use the figures from the previous sheetMedy coordsLine to edge of box at LQ5Up to top of box, box height 1, so 0.5 up from middleAlong to end of boxBack to medianDown to bottomTo left edge
To right edgeDraw the endBack to the middleThe end whiskerWNow select the two columns of figures and plot xy scatter graph. Rescale as appropriateDown to bottom of boxCThe start point. I've used ycoordinate 1 for the middle of the boxBack to back box plots can be achieved. A second box could use y coordinates from 2 to 3 or 2.5 to 3.5, and a third could use the next etc..3Remember to add keys so it is clear which is which.wYou will need to switch horizontal gridlines off to view it best. Reformat to make it clear. No need for vertical scaleOriginalPossible reformatlNow use Data > Sort. Make sure 'My list has header row' is selected, and choose Year Group and then Gender.=>>8Now select all the * data and copy it to a new worksheet:You will need the original data again, so just levae that.{An initial sample can be taken. Quite large 50100 and stratified to take account of different year group sizes and gender.jIf you wish to compare say year 7 boys with year 7 girls you will need a new sample, of say 3050 of each.Year of BirsthAge
Years to liveLife expectancygTry to extract a straified sample of 30. Plot a scatter graph of Year of birth against life expectancy.What would your hypothesis be?4Now compare 1948 with 1965 and 1987 using box plots.DAgain what would your hypothesis be and why would you use box plots?What do your box plots show?b+ K:
W
4
1},
^@"ce!!
dMbP?_*+%"??U"
~
$@
`d@N@~
$@
?F@~
&@
f@N@~
&@
d@J@~
$@
f@H@~
$@
d@Q@~
$@
g@Q@~
&@
d@K@~
$@
c@F@~
&@
@d@L@~
&@
`c@B@~
$@
f@N@~
$@
d@P@~
&@
d@P@
L
882222222222222288!
!">@ 7,?4?MaleFemalem
^@>#',
dMbP?_*+%"??FeUColumn L
P
~
&@
d@P@~
&@
`e@L@
~
&@
`e@L@
~
&@
f@N@
~
&@
c@N@~
&@
f@R@
~
&@
e@O@~
&@
d@P@
~
&@
g@T@~
&@
c@N@
~
&@
f@L@~
&@
e@O@
~
&@
f@N@
M~
&@
g@T@
~
&@
@g@L@~
&@
f@L@
~
&@
f@R@
~
&@
@g@L@
~
&@
?N@~
&@
?N@
~
&@
g@@R@~
&@
g@@R@
~
&@
e@M@~
&@
e@M@
~
&@
`@F@~
&@
`@F@
~
&@
@d@J@~
&@
@d@J@
~
&@
@d@L@~
&@
@d@L@
N
O
6rrddddrdddddd>@7
^@T.
dMbP?_*+%"??FeU
Q
>@7
^@/3
dMbP?_*+%"??FeU}I
/~
d@#gffff9@
%

.~
`e@~
c@#](\?
%B
~
e@~
g@# Q?
%B
!
"~
f@~
f@%RQ? %AG
#
$~
@g@%Q?%AG
%
&~
f@%
zG?%AG
(
'~
?~
g@#Q?
%B
)
*~
e@#(\?
%B
+
,~
`@~
@d@~
@d@$>,QQQSSSQQ>@ 7
^@.5l99
dMbP?_*+%"??U.
0
1
2
7
9
3
`@?
F
4d@?
:d@?
E
d@?
;
5f@?
<
8e@?
=
8e@?
>d@?
?f@?
@f@?
Af@?
B
6g@?
C
D
I
G
H
J0H22$$222$$$$2

K"8 (
p
6NMM?, p:x]`P ^@MDEPSON Stylus Photo 1290`f 4dhh4***qhhDLLName32=E_DU15CE.DLL LdL2EPSON Stylus Photo 1290 ***" d??3`
/`
/`
/3d23 M
NM43Q
Year 11 BoysQ;
Q;
Q3_
NM MM<4E4D$% M3O&Q4$% M3O&Q4FAO3O3*?#M!43* &!M4523
NM43d"
S3O% M3OQ443_ M
NM MM<444% Mp3OQ'44eee
xp
6NMM?p+]`LP ^@"L??3`
/ `
/
n迷3d23 M
NM43QQ;
Q;
Q3_4E4D
$% M3O& Q4$% M3O&
Q4FA$TZ3OTvL3*#M43*#M! M4523
O43d"
%
YC3O%
% M3OQ443_ M
NM MM<4444eee
>@0
00
7
^@:IRZ
dMbP?_*+%"
??U:
R
S
T
U~
p@
<@<@~
p@
?P@P@~
p@
@`@T@
V~
p@
$@@h@
W~
p@
.@@@~
p@
4@Բ@@
X~
p@
9@@ֺ@
Y~
p@
>@@@
Z~
p@
A@@0@~
p@
D@@l@~
p@
F@:@Q@~
p@
I@D@*@~
p@
K@@@~
p@
N@@j@~
p@
@P@8@2@~
p@
Q@P@"@~
p@
R@@T@~
p@
T@@`U@~
p@
@U@ w@S@~
@
@@~
@
?r@ֺ@~
@
@P@@Q@~
@
$@@@~
@
.@6@@~
@
4@`@0@~
@
9@F@Q@~
@
>@@@~
@
A@@@~
@
D@t@ڻ@~
@
F@;@R@~
@
I@@z@D lF88FF8FFF8888888888888888888888 !"#$%&'()*+,./0123456789~
@
K@@@~
!@
! !N@ؘ@@~
"@
" "@P@(@n@~
#@
# #Q@@@h@~
$@
$ $R@P@;@~
%@
% %T@`@@~
&@
& &@U@@`V@~
'@
' '@@~
(@
( (?@\@~
)@
) )@@p@~
*@
* *$@@p@~
+@
+ +.@@z@~
,@
, ,4@Ҵ@@~
@
 9@@ʼ@~
.@
. .>@:@@~
/@
/ /A@@$@~
0@
0 0D@l@V@~
1@
1 1F@@@~
2@
2 2I@@ؽ@~
3@
3 3K@l@2@~
4@
4 4N@@@~
5@
5 5@P@@T@~
6@
6 6Q@@ @~
7@
7 7R@`@@~
8@
8 8T@0@@~
9@
9 9@U@@@88888888888888888888888888>@
7
Oh+'0@H`x
STAFFCOOMBESSxSTAFFCOOMBESSxMicrosoft Excel@Z@]՜.+,0< PXx
Research Machines plc. Initial preparation SamplingFurther investigationStatistics
Box plotsNow you try it.Worksheets
!"#$%&'()*+,/012345789:;<=Root Entry F^eWorkbookd[SummaryInformation(.DocumentSummaryInformation86