7

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?

SU 05024 example

Improve this question
  • @pnuts sorry means between 15 and 25. – james Jun 7 '13 at 18:23
  • Probably what you really want is a median... – BlueRaja - Danny Pflughoeft Jun 7 '13 at 22:33
6

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

Improve this answer
  • When i pasted the formula it return the zero.. – james Jun 7 '13 at 18:33
  • 1
    This works for me. Remember to update calculations (F9) and ensure the cell references are correct to the sheet you are working with. – CharlieRB Jun 7 '13 at 19:03
2

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.

Improve this answer
  • It return me the zero answer ..why is it so? – james Jun 7 '13 at 18:18
  • This also works for me. Remember to update calculations (F9) and ensure the cell references are correct to the sheet you are working with. – CharlieRB Jun 7 '13 at 19:04
2

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.