+ Reply to Thread
Results 1 to 11 of 11

Calculating Averageif with OR statement

  1. #1
    Registered User
    Join Date
    03-12-2013
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    4

    Calculating Averageif with OR statement

    To create a simple example, a three column spreadsheet. Column A is Student Name, Column B is grade (1 through 100), column C is Assignment type (Test, Paper, Quiz, or Worksheet).

    I want to use Averageifs to find the combined average of all tests and quizzes for a given student.

    I have tried using an array formula
    Please Login or Register  to view this content.
    I have tried using OR() in an averageifs, but cannot get the syntax to work.

    Any chance someone could offer some insight here?

    Thanks for your help!

  2. #2
    Registered User
    Join Date
    03-02-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    87

    Re: Calculating Averageif with OR statement

    Hi cgrater!

    Does not look like it's going to work... There's no If TRUE or false.... and what is it going to AVERAGE.....

    Jason...

  3. #3
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Calculating Averageif with OR statement

    Try this
    {=AVERAGE(IF(($A$1:$A$9="Bob")*(($C$1:$C$9="Test")+($C$1:$C$9="Quiz")),$B$1:$B$9))}

    or
    =SUMPRODUCT(--($A$1:$A$9="Bob"),($C$1:$C$9="Test")+($C$1:$C$9="Quiz"),$B$1:$B$9)/SUMPRODUCT(--($A$1:$A$9="Bob"),($C$1:$C$9="Test")+($C$1:$C$9="Quiz"))

    ANDS and ORS and Array formulas don't always work the way you'd think they would.
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  4. #4
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    3,256

    Re: Calculating Averageif with OR statement

    Have you tried a pivot table?
    Attached Files Attached Files
    Willem
    English is not my native language sorry for errors
    Please correct me if I'm completely wrong

  5. #5
    Registered User
    Join Date
    03-12-2013
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Calculating Averageif with OR statement

    Sorry, I may have been less clear than I thought.

    For the example:
    I want the average Grade (range B:B) when Student (range A:A) = "Bob" and Assignment (range C:C) = "Test" OR = "Quiz".

    Sample file attached
    Attached Files Attached Files
    Last edited by cgrater; 03-12-2013 at 08:50 AM.

  6. #6
    Registered User
    Join Date
    03-12-2013
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Calculating Averageif with OR statement

    Quote Originally Posted by popipipo View Post
    Have you tried a pivot table?
    A pivot table would work for the small sample set.

    However, there are two things that make a pivot table less helpful:
    1 - size. The database I am using will have upwards of 100,000 scores for more than 1000 students. There are 13 different assignment types, and I need to be able to calculate multiple hybrid averages.

    2 - final use. On another sheet in this workbook, I have a Report Card template (which includes data from other worksheets). I have a macro that runs one iteration for each unique student name, and saves the result as a pdf. Each time the macro runs, it changes one cell in the template (student name) to the next name in sequence. The rest of the values in the template recalculate by referencing that one cell. While my understanding of pivot tables is somewhat limited, I believe that this would cause some issues, not least of which would be slowdown, since a pivot-table approach would include adding lines to the macro to change the filters in the pivot table to get the value for each hybrid average I need to calculate?

  7. #7
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    3,256

    Re: Calculating Averageif with OR statement

    An array of sumproduct formula don't work to with such a large database.
    Maybe you should use a real database program such as ACCESS.

  8. #8
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Calculating Averageif with OR statement

    Quote Originally Posted by cgrater View Post
    Sorry, I may have been less clear than I thought.
    Did you try ChemistB's suggestions, they should both work for you - AVERAGEIFS cannot be used in an "OR" scenario. Another approach would be to use this "array formula" in F3

    =AVERAGE(IF(A$2:A$100=F3,IF(C$2:C$100={"Test","Quiz"},B$2:B$100)))

    confirm with CTRL+SHIFT+ENTER so that curly braces like { and } appear around the formula in the formula bar.....copy to F4
    Audere est facere

  9. #9
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Calculating Averageif with OR statement

    I thought you were clear. Here is your sheet with my formula (the first one) filled in G3:H4
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    03-12-2013
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Calculating Averageif with OR statement

    Worked perfectly, and it worked in the larger file.

    Yes, an Access database would probably work better. Anyone want to volunteer to try to convince my CFO that I need to buy a copy of Access?

    Thank you all for your (really fast) help!

  11. #11
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Calculating Averageif with OR statement

    Quote Originally Posted by cgrater View Post
    The database I am using will have upwards of 100,000 scores for more than 1000 students.
    For large datasets, because AVERAGEIFS won't work, the next best approach (in terms of efficiency/speed of formulas) would probably be to employ a SUMIFS/COUNTIFS formula, e.g.

    =SUM(SUMIFS(B:B,A:A,F3,C:C,{"test","quiz"}))/SUM(COUNTIFS(A:A,F3,C:C,{"test","quiz"}))

+ 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