Averageif function with range that includes non-consecutive cells

  • Thread starter Daniel Collison
  • Start date
D

Daniel Collison

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,
 
J

Jacob Skaria

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
 
T

T. Valko

One way...

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

T. Valko

Try this...

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

Daniel Collison

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top