Average Cells IF They Fit Criteria
 

I need to average ranges of cells in a column, only if they greater than a
certain value. For instance, in the following column, I need to average only
the cells that are greater than 5. Is there an easy way to do this?

1
2
1
1
5.5
6
8
9
1
1
2
3
6
7
6
8

Average Cells IF They Fit Criteria
 

Assuming those numbers are in column A, you can use this array*
formula:

=AVERAGE(IF(A1:A165,A1:A16))

An array formula needs to be committed using the key combination of
Ctrl-Shift-Enter (CSE) rather than the usual <enter. If you do this
correctly then Excel will wrap curly braces { } around the formula
when viewed in the formula bar - do not type these yourself. If you
edit the formula you will need to use CSE again.

Hope this helps.

Pete

On Oct 13, 2:05*am, Eric H wrote:
I need to average ranges of cells in a column, only if they greater than a
certain value. For instance, in the following column, I need to average only
the cells that are greater than 5. Is there an easy way to do this?

1
2
1
1
5.5
6
8
9
1
1
2
3
6
7
6
8

Average Cells IF They Fit Criteria
 

you can try =AVERAGEIF(A11:A19,"5") as well

"Pete_UK" wrote:

Assuming those numbers are in column A, you can use this array*
formula:

=AVERAGE(IF(A1:A165,A1:A16))

An array formula needs to be committed using the key combination of
Ctrl-Shift-Enter (CSE) rather than the usual <enter. If you do this
correctly then Excel will wrap curly braces { } around the formula
when viewed in the formula bar - do not type these yourself. If you
edit the formula you will need to use CSE again.

Hope this helps.

Pete

On Oct 13, 2:05 am, Eric H wrote:
I need to average ranges of cells in a column, only if they greater than a
certain value. For instance, in the following column, I need to average only
the cells that are greater than 5. Is there an easy way to do this?

1
2
1
1
5.5
6
8
9
1
1
2
3
6
7
6
8

Average Cells IF They Fit Criteria
 

Hi,

If you use Excel2007,
=AVERAGEIF(A1:A100,"5")
where A1:A100 is the data range. Change the range appropriately for your
data.

If you are using an earlier version of Excel,
=SUMIF(A1:A100,"5")/COUNTIF(A1:A100,"5")

Regards,
B. R. Ramachandran

"Eric H" wrote:

I need to average ranges of cells in a column, only if they greater than a
certain value. For instance, in the following column, I need to average only
the cells that are greater than 5. Is there an easy way to do this?

1
2
1
1
5.5
6
8
9
1
1
2
3
6
7
6
8