Average together two columns, including only numbers between two values

[labratto]

Hello all,

I am trying to get an average of two non-consecutive columns, and only include values between 80 and 120. In the screenshot below, I would like to average the values in columns I and K. I also need to exclude any "" cells and cells that may contain #N/A
Thanks!

Screenshot 2021-02-05 155746.png

[labratto]

I attached a sample of the data below.

[ChemistB]

Are you looking to average each row individually (i.e. Average(I5,K5) or average all values?

[labratto]

Average all the values together.

[Gregb11]

Does this give you what you want? (the average based on your conditions is 102.375

=AVERAGE(FILTERXML("<x><m>"&TEXTJOIN("</m><m>",,FILTER($I$5:$I$26,($I$5:$I$26>=80)*($I$5:$I$26<=120)),FILTER($K$5:$K$26,($K$5:$K$26>=80)*($K$5:$K$26<=120)))&"</m></x>","//m"))

[bebo021999]

Try to combine two AVERAGE's of two ranges

Please Login or Register  to view this content.

[labratto]

Does this give you what you want? (the average based on your conditions is 102.375

=AVERAGE(FILTERXML("<x><m>"&TEXTJOIN("</m><m>",,FILTER($I$5:$I$26,($I$5:$I$26>=80)*($I$5:$I$26<=120)),FILTER($K$5:$K$26,($K$5:$K$26>=80)*($K$5:$K$26<=120)))&"</m></x>","//m"))

This seems to work for my sample data set but when I try to change the ranges for more data, it returns an error. For example, I applied this to another sheet and it returns #value!

=AVERAGE(FILTERXML("<x><m>"&TEXTJOIN("</m><m>",,FILTER($I$11:$I$7388,($I$11:$I$7388>=80)*($I$11:$I$7388<=120)),FILTER($F$11:$F$7388,($F$11:$F$7388>=80)*($F$11:$F$7388<=120)))&"</m></x>","//m"))

What am I missing?

[labratto]

Try to combine two AVERAGE's of two ranges

Please Login or Register  to view this content.

Its close but not the the exact value.

[Gregb11]

I applied this to another sheet and it returns #value!

Its close but not the the exact value.

Which is it, close but not exact, or returns #value!

It may be because it's several thousand rows - I'm not sure that formula works for large amounts.
Can you post the spreadsheet?

[labratto]

bebo021999's suggestion of averaging two averages is close but not the correct answer.
Your suggestion works for the small data set but not the larger one.

Here is another data set.

[Gregb11]

How about this?

=(SUMIFS(F11:F3877,F11:F3877,">=80",F11:F3877,"<=120") + SUMIFS(I11:I3877,I11:I3877,">=80",I11:I3877,"<=120")) / (COUNTIFS(F11:F3877,">=80",F11:F3877,"<=120") + COUNTIFS(I11:I3877,">=80",I11:I3877,"<=120"))

[labratto]

How about this?

=(SUMIFS(F11:F3877,F11:F3877,">=80",F11:F3877,"<=120") + SUMIFS(I11:I3877,I11:I3877,">=80",I11:I3877,"<=120")) / (COUNTIFS(F11:F3877,">=80",F11:F3877,"<=120") + COUNTIFS(I11:I3877,">=80",I11:I3877,"<=120"))

This works, thank you!