+ Reply to Thread
Results 1 to 6 of 6

Average with one set column value and two OR values

  1. #1
    Registered User
    Join Date
    01-30-2019
    Location
    Nashville, TN
    MS-Off Ver
    365
    Posts
    8

    Average with one set column value and two OR values

    I have a large amount of data that I am analyzing with specific criteria subsets. I originally thought I could utilize the averagifs function, but found that it cannot contain an OR element. I believe that an array might be the right path, but am having trouble getting it sorted correctly.

    I have two columns in my data that I am using to identify data sets with (these two sets identify a location in a grid of data), an x and y coordinate you could say.

    I am hoping to select one "x" value and TWO "y" values to include in the average calculation. eg: Calculate the average for all "x=2" AND "y=2 or 3".

    Appreciate the guidance.
    Attached Files Attached Files
    Last edited by philfry; 01-30-2019 at 07:16 PM. Reason: Updated to include a sample worksheet.

  2. #2
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Average with one set column value and two OR values

    Please try

    =SUMPRODUCT(SUMIFS(J2:J72,B2:B72,N5,C2:C72,O5:P5))/SUMPRODUCT(COUNTIFS(B2:B72,N5,C2:C72,O5:P5))
    or
    =SUMPRODUCT((B2:B72=N5)*(C2:C72=O5:P5)*J2:J72)/SUMPRODUCT((B2:B72=N5)*(C2:C72=O5:P5))

    or this if not include blank average.
    =SUMPRODUCT(SUMIFS(J2:J72,B2:B72,N5,C2:C72,O5:P5))/SUMPRODUCT(COUNTIFS(B2:B72,N5,C2:C72,O5:P5,J2:J72,"<>"))

    =SUMPRODUCT((B2:B72=N5)*(C2:C72=O5:P5)*J2:J72)/SUMPRODUCT((B2:B72=N5)*(C2:C72=O5:P5)*(J2:J72<>""))

  3. #3
    Registered User
    Join Date
    01-30-2019
    Location
    Nashville, TN
    MS-Off Ver
    365
    Posts
    8

    Re: Average with one set column value and two OR values

    Ahhhh. I was trying to complicate it a bit too much. I appreciate the help. Worked like a charm.

  4. #4
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Average with one set column value and two OR values

    Hello philfry. Welcome to the forum.

    Do I understand correctly? Wouldn't this
    Calculate the average for all "x=2" AND "y=2 or 3".
    return 2 averages ... in an array?
    Dave

  5. #5
    Registered User
    Join Date
    01-30-2019
    Location
    Nashville, TN
    MS-Off Ver
    365
    Posts
    8

    Re: Average with one set column value and two OR values

    Quote Originally Posted by FlameRetired View Post
    Hello philfry. Welcome to the forum.

    Do I understand correctly? Wouldn't this return 2 averages ... in an array?
    Thank you for the welcome. It looks like using the suggestion above worked. I was looking to average values from a large amount of data that contain grid identifiers. Wanted to use a function to identify data that matched 2,6 and 2,7 (these are x and y coordinates in an experimental setup that happen to have the same conditions) into one average value.

    Looking forward to more on the forums. Thanks!

  6. #6
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Average with one set column value and two OR values

    You are welcome. Thank you for the feedback and marking your thread Solved.

+ 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] Getting average of values in a column leaving out values in some cells
    By Anuru in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-21-2015, 07:07 AM
  2. Replies: 4
    Last Post: 11-19-2014, 10:13 PM
  3. Replies: 2
    Last Post: 06-06-2013, 08:29 AM
  4. [SOLVED] If values in a column are equal, then need to calc average of values in other columns
    By engineerlady in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-15-2013, 02:33 PM
  5. Replies: 4
    Last Post: 12-12-2012, 10:37 AM
  6. Average range of cells in column if values in adjacent column are equal
    By RyNye in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-09-2012, 10:12 AM
  7. Replies: 8
    Last Post: 02-16-2011, 05:03 PM

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