Find average
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.
|