Averaging select numbers in a column

[dmjames]

Hi,

I am lookng to average only selected numbers in a column dependent on the entry in another column.

column A ...........................column B

AMOUNT...........................STATUS
3.5.....................................active
0.4.....................................inactive
0.5 .....................................active
2.4..................................... pending
12.5.....................................active
6.7 ..................................... pending
3.1 ..................................... pending
3.4..................................... inactive
8.5..................................... active
3.0..................................... inactive


I can not figure out how to create a formula that will only average the numbers in column A that have an "ACTIVE" status in column B.

Any suggestions would be greatly apprciated.

[Paul]

Hi Dmjames, welcome to the forum.

In Excel 2003 use the array formula:

=AVERAGE(IF(B1:B10="active",A1:A10))

This must be confirmed with CTRL+SHIFT+ENTER, not just ENTER.

In Excel 2007 you can use the new AVERAGEIF function (non-array), i.e.

=AVERAGEIF(B1:B10,"active",A1:A10)

[darkyam]

Edit: Removed. Didn't notice that OP was on 2003 before I posted and Paul's post covers both 2003 and 2007 versions.