# Exclusive Averaging

#### JKFEagle

##### New Member
I'm looking for a way to average a series of numbers, filtering out everything outside a min and max. The data cannot be manipulated, sorted, etc. because it is linked to another file. Any ideas?

### Excel Facts

Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

#### Scott Huish

##### MrExcel MVP
Not sure what you're asking exactly, every number would be between the minimum and the maximum number. Please clarify.

#### DonkeyOte

##### MrExcel MVP
entered as an array where > should be min and < should be max, alter range as required.

=AVERAGE(IF((A2:A10>1)*(A2:A10<5),A2:A10))

commit as array using SHIFT + CTRL + ENTER

will appear as

{=AVERAGE(....)}

#### Cindy Ellis

##### MrExcel MVP
Without details of data location, size, etc., the following will have to be modified appropriately to fit your data and situation. I'm also not sure of the extent of user interaction vs. "hard-coded" information that you need, so please reply if the following doesn't do what you need. The following assumes data in cells A1:20, with cell references to the minimum and maximum values to INCLUDE in the calculation in D8 (minimum) and D9 (maximum):
Code:
``=(SUMPRODUCT((A1:A20)*(A1:A20>=D8)*(A1:A20<=D9)))/((COUNTIF(A1:A20,">="&D8))-(COUNTIF(A1:A20,">"&D9)))``
If you need help modifying this to fit your data, just reply back.
Cindy

Replies
7
Views
329
Replies
4
Views
183
Replies
3
Views
2K
Replies
6
Views
307
Replies
2
Views
149

1,191,226
Messages
5,985,373
Members
439,961
Latest member
drose1105

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

### Which adblocker are you using?

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

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