Exclusive Averaging

[JKFEagle]

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?

 

[Scott Huish]

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

 

[DonkeyOte]

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]

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):

=(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