AverageIf
 

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.

AverageIf
 

Maybe

=AVERAGE(IF($A$1:$A$152,IF(A1:A15<8,A1:A15)))

Array entered with Ctrl+Shift+Enter

Mike

"TG" wrote:

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.

AverageIf
 

This is an array formula that must be entered using ctrl+shift+enter
=AVERAGE(IF((H2:H222)*(H2:H22<6),H2:H22))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"TG" wrote in message
...
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.

AverageIf
 

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

"TG" wrote:

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.

AverageIf
 

Here is yet a different way
=SUMPRODUCT(--(F6:F152),--(F6:F15<8),F6:F15)/SUMPRODUCT(--(F6:F152),--(F6:F15<8))

If you want to include either 2 or 8 change the inequality to = or <=
respectively.
--
Wag more, bark less


"TG" wrote:

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.

AverageIf
 

You guys all rock. All these methods work and are exactly what I was after.

Thank you!

"Brad" wrote:

Here is yet a different way
=SUMPRODUCT(--(F6:F152),--(F6:F15<8),F6:F15)/SUMPRODUCT(--(F6:F152),--(F6:F15<8))

If you want to include either 2 or 8 change the inequality to = or <=
respectively.
--
Wag more, bark less


"TG" wrote:

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.