Averageif function with range that includes non-consecutive cells
 

I am trying to average a range of numbers if the values are less than or
equal to 5. The range includes non-consecutive cells. I have attempted two
different versions of the averageif formula (see below). Both versions
return a #VALUE error.

=AVERAGEIF((P6,R6,S6,T6,U6,V6,X6,Y6,Z6),"<=5")
=AVERAGEIF((P6,R6:V6,X6:Z6),"<=5")

Thanks,

Averageif function with range that includes non-consecutive cells
 

Alternative

=SUM(SUMIF(INDIRECT({"P6","R6:V6","X6:Z6"}),"<=5") )/
SUM(COUNTIF(INDIRECT({"P6","R6:V6","X6:Z6"}),"<=5" ))

If this post helps click Yes
---------------
Jacob Skaria


"Daniel Collison" wrote:

I am trying to average a range of numbers if the values are less than or
equal to 5. The range includes non-consecutive cells. I have attempted two
different versions of the averageif formula (see below). Both versions
return a #VALUE error.

=AVERAGEIF((P6,R6,S6,T6,U6,V6,X6,Y6,Z6),"<=5")
=AVERAGEIF((P6,R6:V6,X6:Z6),"<=5")

Thanks,

Averageif function with range that includes non-consecutive cells
 

One way...

=(SUMIF(R6:V6,"<=5")+SUMIF(X6:Z6,"<=5")+(P6<=5)*P6 )/INDEX(FREQUENCY((P6,R6:V6,X6:Z6),5),1)

--
Biff
Microsoft Excel MVP


"Daniel Collison" wrote in
message ...
I am trying to average a range of numbers if the values are less than or
equal to 5. The range includes non-consecutive cells. I have attempted
two
different versions of the averageif formula (see below). Both versions
return a #VALUE error.

=AVERAGEIF((P6,R6,S6,T6,U6,V6,X6,Y6,Z6),"<=5")
=AVERAGEIF((P6,R6:V6,X6:Z6),"<=5")

Thanks,

Averageif function with range that includes non-consecutive cells
 

Try this...

=(SUMIF(R6:V6,"<=5")+SUMIF(X6:Z6,"<=5")+(P6<=5)*P6 )/INDEX(FREQUENCY((P6,R6:V6,X6:Z6),5),1)



--
Biff
Microsoft Excel MVP


"Daniel Collison" wrote:

I am trying to average a range of numbers if the values are less than or
equal to 5. The range includes non-consecutive cells. I have attempted two
different versions of the averageif formula (see below). Both versions
return a #VALUE error.

=AVERAGEIF((P6,R6,S6,T6,U6,V6,X6,Y6,Z6),"<=5")
=AVERAGEIF((P6,R6:V6,X6:Z6),"<=5")

Thanks,

Averageif function with range that includes non-consecutive ce
 

Jacob: The formula worked great. However, the data for which the
calculation was intended spanned 1,400 rows. I tried to copy the formula to
the other 1,399 rows, but the reference to row 6 remained the same.

However, the formula that T. Valko posted worked great and I was able to
successfully copy to other rows.

Thanks for your input!

Daniel

"Jacob Skaria" wrote:

Alternative

=SUM(SUMIF(INDIRECT({"P6","R6:V6","X6:Z6"}),"<=5") )/
SUM(COUNTIF(INDIRECT({"P6","R6:V6","X6:Z6"}),"<=5" ))

If this post helps click Yes
---------------
Jacob Skaria


"Daniel Collison" wrote:

I am trying to average a range of numbers if the values are less than or
equal to 5. The range includes non-consecutive cells. I have attempted two
different versions of the averageif formula (see below). Both versions
return a #VALUE error.

=AVERAGEIF((P6,R6,S6,T6,U6,V6,X6,Y6,Z6),"<=5")
=AVERAGEIF((P6,R6:V6,X6:Z6),"<=5")

Thanks,