Average all values in two different columns that are between two values?

[labratto]

Hello all,

I am trying to get an average of values in 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 together. I also need to exclude any "" cells and cells that may contain #N/A. My full data set includes over 7,000 lines.
Thank you!

 

[Eric W]

Welcome to the MrExcel forum!

You could use something like this:

=(SUMIFS(I17:I8000,I17:I8000,">=80",I17:I8000,"<=120")+SUMIFS(K17:K8000,K17:K8000,">=80",K17:K8000,"<=120"))/(COUNTIFS(I17:I8000,">=80",I17:I8000,"<=120")+COUNTIFS(K17:K8000,">=80",K17:K8000,"<=120"))

BUT!!!

It is mathematically incorrect to average percentages. You could come up with some wildly invalid results. You'd need to add up all the numerators of the ratios going into those percentages, and divide by the sum of all the denominators. I couldn't figure out from your picture where those were.

 

[labratto]

Welcome to the MrExcel forum!

You could use something like this:

=(SUMIFS(I17:I8000,I17:I8000,">=80",I17:I8000,"<=120")+SUMIFS(K17:K8000,K17:K8000,">=80",K17:K8000,"<=120"))/(COUNTIFS(I17:I8000,">=80",I17:I8000,"<=120")+COUNTIFS(K17:K8000,">=80",K17:K8000,"<=120"))

BUT!!!

It is mathematically incorrect to average percentages. You could come up with some wildly invalid results. You'd need to add up all the numerators of the ratios going into those percentages, and divide by the sum of all the denominators. I couldn't figure out from your picture where those were.

Its okay for these purposes. This solution works. Any idea how to apply these same rules for calculating the standard deviation?

 

[Eric W]

Possibly

=STDEV.S(IF(ISNUMBER(I17:I8000),IF((I17:I8000>=80)*(I17:I8000<=120),I17:I8000)),IF(ISNUMBER(K17:K8000),IF((K17:K8000>=80)*(K17:K8000<=120),K17:K8000)))

You have Excel 365, so you should be ok. But older versions of Excel would require you to confirm that formula by pressing Control+Shift+Enter, not just Enter.

 

[labratto]

Possibly

=STDEV.S(IF(ISNUMBER(I17:I8000),IF((I17:I8000>=80)*(I17:I8000<=120),I17:I8000)),IF(ISNUMBER(K17:K8000),IF((K17:K8000>=80)*(K17:K8000<=120),K17:K8000)))

You have Excel 365, so you should be ok. But older versions of Excel would require you to confirm that formula by pressing Control+Shift+Enter, not just Enter.

Works great, thanks!