+ Reply to Thread
Results 1 to 22 of 22

How to analysing data within spreadsheet

  1. #1
    Forum Contributor
    Join Date
    11-09-2014
    Location
    Lauder, Scotland
    MS-Off Ver
    Windows 8.1
    Posts
    160

    How to analysing data within spreadsheet

    Okay... Take a deep breath... I understand that this is going to be very tricky to sort and may need to be broken down into a few parts.

    Attached you should find our schools tracking and monitoring module to track learning.

    Within 'Progress Overview' you will see that some data for P1/2 is displayed with regards to their level of understanding/skills in maths (columns J, K, L / Rows 13-29). This data is pulled from another sheet within the workbook. (You will see we are at the early stages of inputting data into this spreadsheet and so there are still a lot of blanks)

    For each curricular area in school we have created a progression of learning and colour coded these. As a rule of thumb the expected level of attainment for each child is:

    Nursery: Light Blue
    P1: Blue
    P2: Purple
    P3: Pink
    P4:Red
    P5: Orange
    P6: Yellow
    P7: Green

    You may notice a very narrow column down the very left hand side of 'Progress Overview' sheet that shows the progression of colours.

    Some children obviously don't reach the expected level when we hoped for and others exceed the expected level.

    What my wish now is for some sort of formula that will work out the number of children for each cohort (year Group) that are below, on track, or above the expected level for maths.

    Back at the title screen you will see another hyperlink button titled 'How well are we doing in... Maths'. Is it possible in any way, shape or form for the information on Maths to be collected and pinged through to this sheet????

    I understand spreadsheets can do a lot for us but I am pretty certain I am maybe asking a bit too much this time!

    If you can help or provide any advice as to how I can complete even a small part of this then please help. My school and I would be EXTREMELY grateful for your support!

    THANK YOU
    Attached Files Attached Files
    Last edited by kevwilsondj; 03-25-2015 at 07:07 PM. Reason: misunderstanding about title

  2. #2
    Forum Contributor
    Join Date
    06-21-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    191

    Re: THIS IS A HARD ONE!! Analysing data within my spreadsheet

    IN looking at C4 of the Maths sheet, what number goes there? How will this be determined?

    I assume this is the Broad Overview sheet, Column I, values between 4 and 6. The trouble with that, however, is that you want this by year groups, and I don't see a data set that contains those year groups.

  3. #3
    Forum Contributor
    Join Date
    11-09-2014
    Location
    Lauder, Scotland
    MS-Off Ver
    Windows 8.1
    Posts
    160

    Re: THIS IS A HARD ONE!! Analysing data within my spreadsheet

    Hi soberguy thanks for getting in touch. Just realised that the sheet is titled different to the hyperlink on the contents page. Yeah its the sheet titled 'Broad Overview'. No sorry it is not column I- this is a summative assessment that has already trafficlighted by red amber and green how well the children are doing.

    What I am looking for is for is information to be collected from columns J, K & L. This is our on-going Maths assessments which are more reliable.

    Was thinking, perhaps, a hidden column after column L would be needed first. Then a formula to average out the colours as maths is broken down into Information Handling, Number Money & Measurement, and Shape Position & Movement but what I need first is an average for how each child is doing in Maths. This would be displayed with a colour (however there are two hidden sheets- one of these is a lookup table so that each colour is assigned a value.

    I already have a formula for averaging colours in a hidden column (K) within sheet titled Maths.

    The formula for this column is:
    =IFERROR(INDEX(Sheet2!$A$1:$A$9,
    MATCH(ROUND(AVERAGE(INDEX(Sheet2!$B$1:$B$9,MATCH(Maths!F38,Sheet2!$A$1:$A$9,0)),
    INDEX(Sheet2!$B$1:$B$9,MATCH(Maths!G38,Sheet2!$A$1:$A$9,0)),
    INDEX(Sheet2!$B$1:$B$9,MATCH(Maths!H38,Sheet2!$A$1:$A$9,0)),
    INDEX(Sheet2!$B$1:$B$9,MATCH(Maths!I38,Sheet2!$A$1:$A$9,0)),
    INDEX(Sheet2!$B$1:$B$9,MATCH(Maths!J38,Sheet2!$A$1:$A$9,0))),0),Sheet2!$B$1:$B$9,0)),"")


    That means nothing to me but may provide you with idea of how to do the first step.

    I am so grateful for your support. Thank you

  4. #4
    Forum Contributor
    Join Date
    02-04-2014
    Location
    NSW, Australia
    MS-Off Ver
    Excel 2010
    Posts
    136

    Re: THIS IS A HARD ONE!! Analysing data within my spreadsheet

    I would suggest counting colours within each grade?

    Try this site:

    HTML Code: 

  5. #5
    Forum Contributor
    Join Date
    06-21-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    191

    Re: THIS IS A HARD ONE!! Analysing data within my spreadsheet

    OK, so what values will go into J, K, L? What do those values mean in relation to above, achieving, and below?

    And I'll reiterate my question regarding cohort/year group - where is that value found on the Broad Overview page?

    What you want is feasible - once we have an understanding of where the data is to get you there.

  6. #6
    Forum Contributor
    Join Date
    11-09-2014
    Location
    Lauder, Scotland
    MS-Off Ver
    Windows 8.1
    Posts
    160

    Re: THIS IS A HARD ONE!! Analysing data within my spreadsheet

    Hi lcartwright.
    Thanks for getting in touch. It would seem first though I need to average out these three cells to find an average of how each child is doing in maths.
    How can I adapt this:
    =IFERROR(INDEX(Sheet2!$A$1:$A$9,
    MATCH(ROUND(AVERAGE(INDEX(Sheet2!$B$1:$B$9,MATCH(Maths!F38,Sheet2!$A$1:$A$9,0)),
    INDEX(Sheet2!$B$1:$B$9,MATCH(Maths!G38,Sheet2!$A$1:$A$9,0)),
    INDEX(Sheet2!$B$1:$B$9,MATCH(Maths!H38,Sheet2!$A$1:$A$9,0)),
    INDEX(Sheet2!$B$1:$B$9,MATCH(Maths!I38,Sheet2!$A$1:$A$9,0)),
    INDEX(Sheet2!$B$1:$B$9,MATCH(Maths!J38,Sheet2!$A$1:$A$9,0))),0),Sheet2!$B$1:$B$9,0)),"")

    so that it averages columns J, K & L? Surely I only need to adapt a couple of letters?

    Any ideas?

  7. #7
    Forum Contributor
    Join Date
    11-09-2014
    Location
    Lauder, Scotland
    MS-Off Ver
    Windows 8.1
    Posts
    160

    Re: THIS IS A HARD ONE!! Analysing data within my spreadsheet

    Sorry I think I see what you mean with regards to cohort and link on Broad Overview sheet. The children in P1 are cohort 2014-2015, P2 are cohort 2013-2014, P3 are cohort 2012-2013 ... and so on. This is just the year that they entered primary education. Please ignore the lower table on the 'maths' sheet- i no longer need this.

    With regards to the values for J, K and L... it is the colours that are the values. These range from light blue through to green. Each colour is assigned a value in the lookup table that is a hidden sheet. Comparisons need to be made with where the child currently is and where they should be. Where they should be is as follows:

    Nursery: Light Blue
    P1: Blue
    P2: Purple
    P3: Pink
    P4:Red
    P5: Orange
    P6: Yellow
    P7: Green

    I will need to average out where each child is for maths though first eh?

  8. #8
    Forum Contributor
    Join Date
    11-09-2014
    Location
    Lauder, Scotland
    MS-Off Ver
    Windows 8.1
    Posts
    160

    Re: THIS IS A HARD ONE!! Analysing data within my spreadsheet

    Here is an updated file. I've just been making some minor changes- left a space for averaging maths.
    Attached Files Attached Files

  9. #9
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,572

    Re: THIS IS A HARD ONE!! Analysing data within my spreadsheet

    Title modified, but...
    Last edited by protonLeah; 03-25-2015 at 06:26 PM.
    Ben Van Johnson

  10. #10
    Forum Contributor
    Join Date
    06-21-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    191

    Re: THIS IS A HARD ONE!! Analysing data within my spreadsheet

    You're absolutely killing me.

    On the Broad Overview sheet, the values currently in J through L are Blue, Purple, and Light Blue. Now, according to your last post, that equates to Nursery, P1, and P2. Yet you want those values averaged into math scores.

    Where are the math scores, if not in J through L?

    If J through L equates to cohort, why are the cohorts again listed in Column D of the Broad Overview?

    I'd love to help, but I can't find a way to get math scores to average, as the data you have relates to their grade level and not their math scores.

    Edit: Apologies to Leah. I was on the quick reply screen when your post was entered.

  11. #11
    Forum Contributor
    Join Date
    11-09-2014
    Location
    Lauder, Scotland
    MS-Off Ver
    Windows 8.1
    Posts
    160

    How to analyse data within a spreadsheet

    sorry- new to this. Hopefully this title has now been changed. mybad!

  12. #12
    Forum Contributor
    Join Date
    11-09-2014
    Location
    Lauder, Scotland
    MS-Off Ver
    Windows 8.1
    Posts
    160

    Talking Re: How to analyse data within a spreadsheet

    Boom! We in action again.

    Ha ha! Sorry soberguy looks as though I will be driving you to drink if I cant explain this. Please bear with me...

    Right... the maths score ARE the colours! for example... Joe what level you on in Maths? ~Joe then answers purple!

    There are no numbers (although the computer should know through lookup table that purple actually equates to 3. We created this so that we could easily average the colours. I say easily- ive not a clue how! Someone fixed this for me).

    Right as for column D- this just shows what class the children are in so that i can pull off information for each class- but you will see in the sheet titled 'maths' that i refer to the classes as cohort groups. The reason for this is that children will change class each year but their cohort group will remain constant- the year they joined primary education.

    Do you understand that complicated formula that I posted before? This is the key to averaging out the maths colours for each child. This bound to be the first step.

    Don't give up on me soberguy. We have built this international relationship of our for over 1 hour now. That is an hour you will never get back- dont let it go to waste! :-)

  13. #13
    Forum Contributor
    Join Date
    11-09-2014
    Location
    Lauder, Scotland
    MS-Off Ver
    Windows 8.1
    Posts
    160

    Re: THIS IS A HARD ONE!! Analysing data within my spreadsheet

    Am I able to receive posts protonLeah? Sorry

  14. #14
    Forum Contributor
    Join Date
    06-21-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    191

    Re: How to analysing data within spreadsheet

    OK, it's starting to make sense. Sort of.

    You need to explain two more things.

    First of all, what average score is below, what is achieving, and what is above?

    Second, what data on the Broad Overview indicates whether that average goes into 2014-15, 2013-14, etc?

  15. #15
    Forum Contributor
    Join Date
    11-09-2014
    Location
    Lauder, Scotland
    MS-Off Ver
    Windows 8.1
    Posts
    160

    Re: How to analysing data within spreadsheet

    GPS Tracking and Monitoring no namesc.xlsmWoohoo! I thought I had scared you away there for a second soberguy!

    Just to let you know I have now managed to average out the maths to make this easier. Please look at this new sheet then I can explain this a bit better!

  16. #16
    Forum Contributor
    Join Date
    06-21-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    191

    Re: How to analysing data within spreadsheet

    That doesn't answer any of my questions.

  17. #17
    Forum Contributor
    Join Date
    11-09-2014
    Location
    Lauder, Scotland
    MS-Off Ver
    Windows 8.1
    Posts
    160

    Re: How to analysing data within spreadsheet

    Right- you see in column A there are a slither of colours. This is where the children should be for their class. column M now shows an average of where thay actually are for maths.

    So...
    All the P1s are on track apart from Joe who is still operating at Nursery level.
    As for the P2s they are all at blue level for maths but should now be on purple.

    If you look at the sheet titled 'how are we doing in maths?' you will see cohort 2014-2015 has three three reporting columns for this year (below, achieving and above). This is where a percentage (or if this is too difficult then just numbers) is displayed for the P1s. The information for P2s will be displayed on the row below as they are cohort 2013-2014.

    Huge thank you for your time soberguy!!!!

  18. #18
    Forum Contributor
    Join Date
    06-21-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    191

    Re: How to analysing data within spreadsheet

    OK, one last time, and then even this Job has lost his patience.

    Colors equate to numerical values, which are then averaged. For example, Light Blue is 1, and Blue is 2. The average of these scores indicate below, at, or above. What is the threshold for below average? What is the threshold for above average? Anything in between, naturally, would be treated as at grade level, or whatever term you want to apply.

    If you could, please answer the above using numerical values, as opposed to colors.

    The above will help me identify which column the count goes into - C, D, or E.

    However, I am still unclear as to how to determine which ROW these counts will go into - 5, 6, 7, 8, 9, 10, or 11. Where on the Broad Overview is some information that ties each row of data to which row in belongs to in the Maths sheet?

  19. #19
    Forum Contributor
    Join Date
    11-09-2014
    Location
    Lauder, Scotland
    MS-Off Ver
    Windows 8.1
    Posts
    160

    Re: How to analysing data within spreadsheet

    Ok...
    for P1 they should be at 2 (blue). if the are 1(light blue) then they will be below expectation. If they are at 3 (purple) or anything else above this right through to 8 (green) then they are above expectation.

    I can see that I have 11 out of 12 children in P1 on track for their expectation but i have 1 below expectation 0 above expectation (but this may change as year goes on)

    This information would hopefully be shown as a percentage in row 5 of 'how are we doing in Maths' sheet. I need a formula to convert these to percentages and ping through to this other sheet.

    I can't even blame my scottish accent for this communication barrier eh?

  20. #20
    Forum Contributor
    Join Date
    11-09-2014
    Location
    Lauder, Scotland
    MS-Off Ver
    Windows 8.1
    Posts
    160

    Re: How to analysing data within spreadsheet

    what the thoughts soberguy- have I broken you?!

  21. #21
    Forum Contributor
    Join Date
    06-21-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    191

    Re: How to analysing data within spreadsheet

    Not enough information available to provide a solution. You have P1, P2, P3, etc. You apparently have varying metrics for each. Despite repeatedly requesting a cross-walk, one is not provided.

    Further, it remains unclear what data belongs in 14-15, 13-14, 12-13, etc.

    Adding to the above, if Blue is 2, and Light Blue is 1, the average of 2,2,1 is 1.667 - which is less than two. The rounding upwards was previously excluded. Your most recent answer denotes a whole number (and color - which I abhor), and fails to mention that averages should be rounded up.

    I am unable to assist in this matter.

  22. #22
    Forum Contributor
    Join Date
    11-09-2014
    Location
    Lauder, Scotland
    MS-Off Ver
    Windows 8.1
    Posts
    160

    Re: How to analysing data within spreadsheet

    nope you have lost me. I cannot make this any clearer. I will try again with another helper.
    Thanks anyway.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Looking up and analysing duplicate data
    By JONNY981 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-18-2009, 09:57 AM
  2. Analysing data
    By i_ujala in forum Excel General
    Replies: 1
    Last Post: 04-08-2007, 05:29 AM
  3. [SOLVED] Analysing data through pivot
    By Chris in forum Excel General
    Replies: 3
    Last Post: 01-09-2006, 11:45 AM
  4. analysing data from files set
    By Massimiliano in forum Excel General
    Replies: 0
    Last Post: 11-08-2005, 10:25 AM
  5. Help with analysing data required..
    By Timmy Mac1 in forum Excel General
    Replies: 1
    Last Post: 04-08-2005, 06:58 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1