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

labratto

New Member
Joined
Feb 9, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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!
 

Attachments

  • Screenshot 2021-02-05 155746.png
    Screenshot 2021-02-05 155746.png
    101.6 KB · Views: 12

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Welcome to the MrExcel forum!

You could use something like this:

Excel Formula:
=(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.
 
Upvote 0
Solution
Welcome to the MrExcel forum!

You could use something like this:

Excel Formula:
=(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?
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,949
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top