AverageIf

  • Thread starter Thread starter TG
  • Start date Start date
T

TG

Is there a way to average a range of data?

For example: I have a column with values 1,2,3,4,5...10 and I want to
average those values greater than 2 but less than 8.

I *think* the AverageIfs will work but it is my understanding that this
function is only valid in the most current versions of excel. If so, I would
like to find a function that is more compatible.

Thank you.
 
Maybe

=AVERAGE(IF($A$1:$A$15>2,IF(A1:A15<8,A1:A15)))

Array entered with Ctrl+Shift+Enter

Mike
 
This is an array formula that must be entered using ctrl+shift+enter
=AVERAGE(IF((H2:H22>2)*(H2:H22<6),H2:H22))
 
I'll confess my bias against array formulas, and therefore suggest an
alternative, which goes back to the definition of average: total / count.
So I'd calculate the conditional average as sumif/countif:

(sumif(range,">2",sum_range)-sumif(range,">=8",sum_range)) /
(countif(range,">2")-countif(range(">=8"))
 
Here is yet a different way
=SUMPRODUCT(--(F6:F15>2),--(F6:F15<8),F6:F15)/SUMPRODUCT(--(F6:F15>2),--(F6:F15<8))

If you want to include either 2 or 8 change the inequality to >= or <=
respectively.
 
Back
Top