+ Reply to Thread
Results 1 to 10 of 10

AverageIfs based on 3 conditions and third condition may require an OR statement

  1. #1
    Registered User
    Join Date
    03-04-2017
    Location
    NJ
    MS-Off Ver
    Excel for Mac Version 15.31
    Posts
    75

    AverageIfs based on 3 conditions and third condition may require an OR statement

    Hi everyone,

    I got the following dataset and I need to create two averageifs functions that are contigent on 3 conditions. The Final outcomes are two average figures of students' test scores.
    Condition A: Select Rows with "Yes" responses. Do NOT select rows with "No" responses.
    Condition B: Select Rows with value "2" responses.
    Condition C: There's two scenarios for Condition C. In scenario 1, I want to select rows with value of 1 OR value of 1 and any other value (as indicated by a comma).
    Condition C: Scenario 2: I want to select rows with multiple selections with specific constraints: (1) Count if a person has more than 1 selection (as denoted by a comma). (2) However, do not count if the student makes two selections AND the second selection contains number 6. (2) If a student makes more than two selections and there's a 6 in it, then count it.

    Desired outCome #1 with Condition A, B, and first scenario of Condition C should yield an average test score of 84.38. For your convenience, I highlighted the relevant rows in green that go into the final average calculation.
    Desired Outcome #2 with Condition A, B, and second senario of Condition C should yield an average test score of 70 where it's an average of the test scores for IDs 8, 30, 37, 41, 73

    I've tried a variety of methods, as recommended on the internet but my numbers do not match with the expected answers.

    I'd appreciate any assistance/guidance you can provide.

    Thank you.

    Anita
    Attached Files Attached Files

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: AverageIfs based on 3 conditions and third condition may require an OR statement

    I used a Pivot Table as shown in the attached.
    Attached Files Attached Files
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Registered User
    Join Date
    03-04-2017
    Location
    NJ
    MS-Off Ver
    Excel for Mac Version 15.31
    Posts
    75

    Re: AverageIfs based on 3 conditions and third condition may require an OR statement

    Hi Alan,

    Thank you so much for your time and assistance. Unfortunately, I can't use pivot tables to generate the answers. I'm asked to write a formula for each of the two desired outcomes that I had specified that's embedded in a worksheet that contains other formulas for schools and districts to use...Unfortunately I'm so stuck. I spent an entire day trying to figure out and I feel pretty defeated.

    Anyway, I'd appreciate any additional guidance from you. If I could have some type of formula function that can generate the two outcomes(i.e., 84.38 and 70) that would be wonderful. The Schools that I work with have a whole list of excel requests, and I'm already stuck with the first two .

    Thank you again for your time!

    Sincerely,

    Anita

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: AverageIfs based on 3 conditions and third condition may require an OR statement

    I have no other solution for you. Hopefully, someone will jump in here. Is VBA an acceptable alternative?

  5. #5
    Registered User
    Join Date
    03-04-2017
    Location
    NJ
    MS-Off Ver
    Excel for Mac Version 15.31
    Posts
    75

    Re: AverageIfs based on 3 conditions and third condition may require an OR statement

    Thank you Alan for your quick reply. Unfortunately it has to be a formula.
    Anita

  6. #6
    Registered User
    Join Date
    12-03-2012
    Location
    CHENNAI , INDIA
    MS-Off Ver
    Excel 2007
    Posts
    93

    Re: AverageIfs based on 3 conditions and third condition may require an OR statement

    Hi ,

    Please see the attached file.

    I have used helper columns F , G , H , I and J to make the logic clear.

    Using these helper columns , the averages calculated are in cells K1 and L1.

    However , I have also used the conditions and test scores arrays and arrived at the averages in single cells M10 and M12.

    See if this is correct , after changing the data in many ways to test.

    Narayan
    Attached Files Attached Files

  7. #7
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,803

    Re: AverageIfs based on 3 conditions and third condition may require an OR statement

    This formula will give you your desired outcome for scenario 1, however I'm not sure if your Mac version will recognize sumifs and countifs:

    =SUM(SUMIFS($E$2:$E$117,$C$2:$C$117,2,$B$2:$B$117,"Yes",D2:D117,{"*1*",1}))/SUM(COUNTIFS($C$2:$C$117,2,$B$2:$B$117,"Yes",$D$2:$D$117,{"*1*",1}))

    Scenario 2 seems more complicated and I'm tired and going to sleep. Maybe I'll have a chance to look tomorrow night, but scenario 2 will probably be a challenge for me. Hopefully someone else can chime in on that.

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: AverageIfs based on 3 conditions and third condition may require an OR statement

    Anita is this a homework assignment?
    I'm asked to write a formula for each of the two desired outcomes that I had specified
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  9. #9
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    3,198

    Re: AverageIfs based on 3 conditions and third condition may require an OR statement

    For scenario 2 Perhaps
    =AVERAGEIFS(E:E,C:C,2,B:B,"yes",D:D,"<>"&"??6*",D:D,"?,*")

  10. #10
    Registered User
    Join Date
    03-04-2017
    Location
    NJ
    MS-Off Ver
    Excel for Mac Version 15.31
    Posts
    75

    Re: AverageIfs based on 3 conditions and third condition may require an OR statement

    Dear davsth, Gregb11, and NARAYANK991,

    You guys are amazing! Thank you so much for your prompt solutions--they all worked, and now, I'm adding your solutions to my excel cheat sheet for future applications. Also, after googling some of your syntax to help me understand it, I'm learning a lot.

    FDibbins, to answer your question, this is not a homework assignment. I'm a researcher working at a university. I do most of my data work in R but I was recently asked to be involved in another project and since I'm deemed to be the person with the most excel experience, I'm now handling a lot of excel requests from schools, and some of them, such as these, are beyond my excel skill set.

    Thank you again, everyone, for your amazing skills and for your willingness to help!

    Anita

+ 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. [SOLVED] Averageifs month and 3 more conditions
    By pccamara in forum Excel General
    Replies: 3
    Last Post: 11-11-2018, 08:47 AM
  2. [SOLVED] AverageIfs with two conditions in one reference
    By NikhilDixit in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-04-2017, 03:48 AM
  3. Replies: 5
    Last Post: 12-07-2014, 09:16 AM
  4. Excel 2007 : AVERAGEIFS including several conditions
    By milliemoo in forum Excel General
    Replies: 6
    Last Post: 11-16-2011, 11:39 AM
  5. IF AND statement based on condition of another cell
    By Delta729 in forum Excel General
    Replies: 1
    Last Post: 02-25-2011, 01:16 PM
  6. Excel 2007 : AVERAGEIFS - a few conditions put together
    By stojko89 in forum Excel General
    Replies: 6
    Last Post: 05-26-2009, 12:14 AM
  7. Require Cell Entry based on condition
    By Kathy - Lovullo in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-13-2006, 11:10 AM

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