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

#### hkg003

##### Active Member
Hello,

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

Thank You

I used
Code:
``=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.

### Excel Facts

Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

#### Jonmo1

##### MrExcel MVP
If you're using XL2007+, Try

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

Last edited:

#### hkg003

##### Active Member
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?

Last edited:

#### Jonmo1

##### MrExcel MVP
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

##### Active Member
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

##### Active Member
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

##### MrExcel MVP
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

##### Active Member
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:

Code:
``=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

##### MrExcel MVP
The range to average comes first, then criteria ranges+criteria

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

#### hkg003

##### Active Member
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!

Replies
2
Views
188
Replies
8
Views
67
Replies
13
Views
465
Replies
4
Views
231
Replies
5
Views
131

1,195,598
Messages
6,010,644
Members
441,558
Latest member
lambierules

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back