Exclude outliers from average calculation

I want to calculate the average of numbers given in Column A as shown in the figure below. But I want to base the average on values equal to or greater than 15 and equal to or less than 25, via the limits specified in cells E11 and E12. For example, 100 and all those in Column B marked FALSE should be excluded.

How could I achieve this in Excel?

You can use AVERAGEIFS(average_range,criteria_range1,criteria1,criteria_range2,criteria2…) which returns the average (arithmetic mean) of all cells that meet multiple criteria.

Enter this formula in any available cell...

=AVERAGEIFS(A1:A18,A1:A18,"<25",A1:A18,">15")

If your conditional values are in E11 and E12 you can enter this formula...

=AVERAGEIFS(A1:A18,A1:A18,"<"&E12,A1:A18,">"&E11)

which are both equal to 22 in your case.

-I hope this helps

You can use AVERAGEIF in your version of Excel for single conditions.

But, since you have multiple conditions, I suggest you use SUMPRODUCT:

=SUMPRODUCT(($A$1:$A$19>$E$11)*($A$1:$A$19<$E$12)*($A$1:$A$19))/SUMPRODUCT(($A$1:$A$19>$E$11)*($A$1:$A$19<$E$12))

The numerator will return the sum of those numbers in that range that fall in your target range.

The denominator will return the count of those numbers in that range that fall in your target range.

A long winded version that might help with understanding where other answers are not 'performing' as they should or that could be educational, also considers entire columns that may make subsequent expansion trouble-free:

=SUMIFS(A:A,A:A,">="&$E$12,A:A,"<="&$E$11)/COUNTIFS(A:A,">="&$E$12,A:A,"<="&$E$11)

Note that Formulas > Formula Auditing - Evaluate Formula, Evaluate will step through the calculation process and so may help to pin down an errors.

Note answer provided is 21.66667 (with or without value in A19) and that I switched 25 to E11 and 15 to E12.