Skip navigation

How to get the correct information from a spreadsheet

234 Views 1 Reply Latest reply: May 29, 2012 12:36 PM by jaxjason RSS
Gwyn Plem Level 2 Level 2 (160 points)
Currently Being Moderated
May 28, 2012 5:49 AM

Hi,

I have been given a spreadsheet from our local school board. Each year all the 7+ children in the school are tested in numeracy, spelling and reading. They are given a score (a standardised score). If they score a 100 in the test, they are bang on where they school be compared to their age. A score of 104 would be better than their age, and 96 means they are behind. There are also empty cells for whatever reason.

The table I have been given looks like this and has reults in reading, spelling and maths since 2009.

 

NameSchool YearStage of Special needs

 


Reading 2009

Reading 2010Reading 2011reading 2012Spelling 2009Spelling 2010Spelling 2011Spelling 2012Maths 2009Spelling 2010Spelling 2011Spelling 2012

Mickey Mouse

6

s

111

104

104

102

113

113

104

123

114

110

119

110

Donal Duck

6

N

101

102

121

123

104

108

119

119

97

105

110

103

Goofy

Dog

6

 

95

99

114

 

92

100

108

104

95

98

98

87

Daisey Duck

6

A

107

118

122

119

119

119

119

119

125

124

118

121

Pluto Dog

6

 

120

129

130

117

119

119

119

118

121

134

135

112

Minnie Mouse

6

N

117

112

122

115

104

119

119

104

103

106

120

109

 

I have conditional cells rules created so that if a child scores above 100 it is green, white for 95 to 100, amber for a score between 90 and 95, and then red for under 90. But there is so much more information that I cannot figure out how to identify such as -

1) A child that is either deteriorating or improving. This list in full has over 300 names on it. I would like an easy way of figuring if a child is showing signs of deteriorating. He could for example be 115 in 2009, 110 in 2010 and 100 in 2012 - but would still remain in green in the conditional rules. This child needs intervention - but my system makes it difficult to highlight. Also a child might be improving year on year, and additional support can be reduced. Could a conditional rule be created to colour the child's name a certain colour if they have improved/deteriorated more than say 5 numbers? Is there a better way?!! (I am sure there is!!!)

2) I would like a way of highlighting children on different stages of the Special needs register. If a child on the Special Needs register is performing well - it is an achievement in itself, but it is also worrying if he is deteriorating as he already has a lot of support.

3) How do particular years perform - do we have teachers that don't teach spelling/reading/maths as effective? Is there a drop in certain years?

 

 

I have so much fantastic success recently in this board, that I thought I would once again try and ask. I realise that this is a big favour for somebody to undertake, but I though I would try as it would make identifying the children so much easier, and quicker!

Thank you in advance for anybody willing to help me with this.

MacBook Pro, Mac OS X (10.7.2)
  • jaxjason Level 4 Level 4 (3,320 points)

    first thing i would suggest, would be to reorganize your data. The way it is above you have to add columns in between existing columns every time you want to add more data for more years. Sounds like its not much, just one time a year, but it introduces points of possible errors, along with rewritting of your equations each time.

     

    Make Reading, Maths, etc... their own tables. Then create a summary table for the students that would pull data from those individual tables. That way you just add a new column to the end of the table for each new year.

     

    As for the gauging if a student is "trending". I would say look into the Slope formula for a simple answer. It will give you the slope of a line of best fit. Which will show you the overall trend for the student. For special needs I would probably make a new column in your main summary table that looks at both slope and special needs category to determine if that row needs to be flagged.

     

    As for teachers, you would need to find some way to summarize by teacher per year. Since teachers change year over year, this is a bit harder to do. Might take some more thinking...

     

    Hope some of this helps,

    Jason

Actions

More Like This

  • Retrieving data ...

Bookmarked By (0)

Legend

  • This solved my question - 10 points
  • This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.