ExcelBanter - Find average

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Find average (https://www.excelbanter.com/excel-discussion-misc-queries/219018-find-average.html)

I've 2 columns A and B of which Column A contains letters and Column B
contains Numbers. I would like to know a formula which would find the
average of column B but exclude the row if Column A contains "aaa"

Example:
aaa 10
aab 12
aac 10
aad 12
aae 10
aaf 1
aag 0
aah 11
I would want the formula to skip the 1st row which has 'aaa' in column A.

Thanks in advance.
--
Karthi

One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):

=AVERAGE(IF(A1:A8<"aaa",B1:B8))

An Alternative (normally entered):

=SUMPRODUCT(--(A1:A8<"aaa"),B1:B8)/COUNTIF(A1:A8,"<aaa")

In article ,
Karthik wrote:

I've 2 columns A and B of which Column A contains letters and Column B
contains Numbers. I would like to know a formula which would find the
average of column B but exclude the row if Column A contains "aaa"

Example:
aaa 10
aab 12
aac 10
aad 12
aae 10
aaf 1
aag 0
aah 11
I would want the formula to skip the 1st row which has 'aaa' in column A.

Thanks in advance.