+ Reply to Thread
Results 1 to 6 of 6

Average multiple rows based on 2 columns

  1. #1
    Registered User
    Join Date
    11-28-2008
    Location
    British Columbia
    Posts
    3

    Average multiple rows based on 2 columns

    I am trying to sort some marks in order to figure out the mark average. The students have 4-8 classes and these all have to be included in the average. I can sort the data by student number (which is unique for each student) and get all the courses for each student together. Here is what I would like Excel to do:

    1. Search through the student number column and find all the courses for Student-A.

    2. Grab all of the marks for those courses that are listed in another column (Marks) -- for Student-A

    3. Average out all of the marks in order to calculate the student's average mark (A Honours/B Honours) and place that in a third column

    4. I also need to search out effort or study habits (Excellent, Good, Satisfactory, and Needs Improvement) in another worksheet. Students who get all excellent or good - receive an award. Those that get a satisfactory or needs improvement in at least 1 of their courses are disqualified from the award. The same sorting method for the marks could be used - if possible - search out all courses for the student number (1 column), check their effort habit value (2nd column) and put a yes or no in another value (3rd column).

    5. I would like to do this for several hundred students so there is a lot of information

    I have been trying to organze it in Pivot Tables and display the information. I don't have to organize it in Pivot Tables - someone told me it would work...not the case! I do not know whether this is possible or not.

    Any help with this situation is greatly appreciated. Thanks in advance.

    Beaner

  2. #2
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Average multiple rows based on 2 columns

    Can attach an Excel file with a representative sample of the data
    (in the correct columns) and the results you'd like to see calculated?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  3. #3
    Registered User
    Join Date
    11-28-2008
    Location
    British Columbia
    Posts
    3

    Example File

    I put together this file with some random information. The first row is the same information and in the same order. There is no Average column to start off with - but I thought that I might need to add one in order to calculate the average for all the courses that each student takes.

    The information that needs to be calculated would be an average of all the marks. If I added a column just after the marks and enter in this information:

    =AVERAGE(O2:O9)

    But I need to make it a little more intuitive like:

    - searching for all the same student numbers
    - grabbing all of the marks for each course for that student number
    - averaging the values
    - return a value

    All of this in a more automatic manner - there are several hundred students and some have 8 classes while others have only 4.

    Thanks for looking into this. I hope that the file helps. If not let me know any other gaps I should fill in.



    Bean
    Attached Files Attached Files
    Last edited by beaner; 11-29-2008 at 01:25 AM. Reason: a little more information

  4. #4
    Valued Forum Contributor rwgrietveld's Avatar
    Join Date
    09-02-2008
    Location
    Netherlands
    MS-Off Ver
    XL 2007 / XL 2010
    Posts
    1,671

    re: Average multiple rows based on 2 columns

    For the average:
    Please Login or Register  to view this content.
    For the award
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Looking for great solutions but hate waiting?
    Seach this Forum through Google

    www.Google.com
    (e.g. +multiple +IF site:excelforum.com/excel-general/ )

    www.Google.com
    (e.g. +fill +combobox site:excelforum.com/excel-programming/ )

    Ave,
    Ricardo

  5. #5
    Registered User
    Join Date
    11-28-2008
    Location
    British Columbia
    Posts
    3

    Wow

    It works like a charm! That is amazing...I would never have come up with that.

    I appreciate your help with this situation.

    Bean

  6. #6
    Registered User
    Join Date
    04-19-2011
    Location
    Sydney
    MS-Off Ver
    Excel 2007
    Posts
    5

    Smile Re: Average multiple rows based on 2 columns

    Dear Ricardo - how would you do this same fomula if the average was across 2-3 columns with 2 sets of criteria

    If you can please let me know that would be greatly appreciated

    Many Thanks
    Simon

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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