Hi Shane, Remember, people here are helping others in their spare time as a favor. If you have an "urgent" need, sometimes it's better to hire someone for pay. Pressuring strangers to help you urgently in your business life might be asking too much.
That said, I have looked at your spreadsheet, and I have some ideas. Hopefully I understand your question. I'll quote your question as I first read it: > Thanx for your interest ,I feel more positive now that help is on its way > as I am desperate to get these stats in a very accurate way, if u ask the > teachers to do it, they all make invariably some mistake. As you can see > in the example sheet I posted of the grade 9's each learner gets a certain > percentage for each subject, what I want is to know how many students > (learners as they are called here) has which code for each subject. In > other words - instead of asking how many code 1's or code 4's are there > per subject I can also as how many leaners got a percentage between > 20-29 or 40-49. As you can see it is easier to just use the codes as the > report as well as the schedule gives the percentage as well as the code > for each learner/student The file I looked at is the one here: http://www.box.net/shared/et0q8my9qpvjefeciryx Assuming you are referring to the data on the "Schedule" sheet, which seems to have a breakdown of grades for all learners, try this: * Create a new sheet. You could call it "Schedule2". Once you set it up it can be hidden. * Create a simple table on Schedule2 that has one column for each piece of information/field you are interested in, and one row for each detail line from your existing Schedule sheet. This way you will have one row per "record" and one column per "field"rather than the more complicated (though logical) layout you have now. You should end up at a minimum with column headings similar to: LEARNER SUBJECT CODE Now, unless you need just the final grade code for your report, you will also need the following column: TERM * Then you need to populate the rows with data. Create a row on Schedule2 for each Combination of Learner/Subject/Term. Repeat the Learner's name, Subject, and Term # on each row. (If you include the final grade code in your report, you could put the word "Final" in the term column). This could be done either with a macro, or some clever formulas, perhaps utilizing Index, Match, Offset, or Vlookup worksheet functions. If needed, you can add hidden columns to your Schedule sheet that calculate values that those formulas can reference to find the data they need. Then copy the formulas down onto as many rows in Schedule2 as the maximum number of records you will ever need (and then some, perhaps, to be safe). * Then create a Pivot Table report based on Schedule2. it will be able to give you exactly what you are asking for. ===== As an aside, you could consider replacing the hard-coded grade codes throughout your workbook with a formula that calculates the correct code for the grade. This will help insure against errors of inconsistency between grade and code, and allow for a code to be calculated on an aggregated grade -- for example, on a student's total grade for the year, or the average student's grade. The average of a grade code will not necessarily be the code for the average grade. I hope this points you in the right direction. I am happy to try to help you along if you get stuck--just come back with your specific problem or question, and if I can help I will. All the best, Asa From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Shane Allen Sent: Sunday, October 09, 2011 8:38 PM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Working out of Statistics on Exam Results URGENT solution needed! Hi Were u able to make any progress w.r.t. my query?I wish I was able to copy the Grade 9 formulaes but it is not visible Shane HI This is the link to the Grade 8 file for which I want stats to be worked out like the Grade 9 schedule -- ---------------------------------------------------------------------------- ------ Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- ---------------------------------------------------------------------------- ------ Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
