Average a column which does not include blank cells and numbers greater than 10

[hkg003]

Hello,

I need a formula to calculate the average of a column excluding blanks and any number greater than 10.


Thank You

I used

=SUBTOTAL(101,A3:A109)

but I am lost when trying to write a formula to exclude blanks an only average the columns containing 1-10 only excluding the average of any number greater than 10.

 

[Jonmo1]

If you're using XL2007+, Try

=AVERAGEIF(A3:A109,"<=10")

 

[hkg003]

If you're using XL2007+, Try

=AVERAGEIF(A3:A109,"<=10")

Geesh, I feel dumb lol. I am using XL2010 but the formula worked! Thanks again Jonmo1! How about a formula with XL2010 to be able to average a column when it is filtered?

 

[Jonmo1]

Thanks again Jonmo1!

You're welcome

How about a formula with XL2010 to be able to average a column when it is filtered?

This is one of the times that (in my opinion) it's best to use a helper column.
Use an available column, say B for this example.
In B3 filled down to B109 put
=SUBTOTAL(103,A3) - fill down.

Now use
=AVERAGEIFS(A3:A109,A3:A109,"<=10",B3:B109,1)

 

[hkg003]

ADVERTISEMENT

You're welcome


This is one of the times that (in my opinion) it's best to use a helper column.
Use an available column, say B for this example.
In B3 filled down to B109 put
=SUBTOTAL(103,A3) - fill down.

Now use
=AVERAGEIFS(A3:A109,A3:A109,"<=10",B3:B109,1)

Hi Jonmo1,

Since I do not want to average any blanks or numbers greater than 10, dont I need to express that in column B? Right now the formula you provided is also counting anything greater than 10? How do I eliminate that? Also, when filtering, my results do not reflect accurately? Would you look into this? Thank You!

 

[hkg003]

Hi Jonmo1,

Since I do not want to average any blanks or numbers greater than 10, dont I need to express that in column B? Right now the formula you provided is also counting anything greater than 10? How do I eliminate that? Also, when filtering, my results do not reflect accurately? Would you look into this? Thank You!

Nevermind, I figured it out...your formula did address what i was needing and it works! Thank you sooo much!

 

[Jonmo1]

ADVERTISEMENT

You're welcome..


Just FYI, Average and AverageIF/s will ignore blanks by default (text as well).
So you don't need to specify that as a criteria.

 

[hkg003]

You're welcome..


Just FYI, Average and AverageIF/s will ignore blanks by default (text as well).
So you don't need to specify that as a criteria.

Ok one more question...I am trying to return the average to a specific person based on the formula you provided for my team data. I tried the following on a new worksheet in my workbook:

=AVERAGEIFS(Data!$G$3:$G$99999,$A4,Data!$O$3:$O$99999,Data!$O$3:$O$99999,"<=10",Data!AJ3:AJ99999,1)

It returns an error?

Column G of my Data wksht contains the name that is entered in my new wksht in column A. I am wanting to return the average for a specific person.

 

[Jonmo1]

The range to average comes first, then criteria ranges+criteria

=AVERAGEIFS(RangeToAverage,Criteria1Range,Criteria1,Criteria2Range,Criteria2,etc..)

 

[hkg003]

The range to average comes first, then criteria ranges+criteria

=AVERAGEIFS(RangeToAverage,Criteria1Range,Criteria1,Criteria2Range,Criteria2,etc..)

Awesome thank you! I was backwards which was close but thanks for getting me straight lol! Thank you so much Jonmo1!