# Average with one set column value and two OR values

1. ## 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.

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

=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. ## 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. ## 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?

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

Originally Posted by FlameRetired
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. ## Re: Average with one set column value and two OR values

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

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