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
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.

 Name School Year Stage of Special needs Reading 2009 Reading 2010 Reading 2011 reading 2012 Spelling 2009 Spelling 2010 Spelling 2011 Spelling 2012 Maths 2009 Spelling 2010 Spelling 2011 Spelling 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 GoofyDog 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)
• 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

#### More Like This

• Retrieving data ...

#### 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.