Averaging

[JRD]

Is there anyway I can take the average of the numbers in a column but only
including the numbers where the cells in the next door column in the same row
contain a certan word

e.g.

A B
1 Valid 10
2 Invalid 1
3 Invalid 5
4 Valid 5
5 Valid 3

How to I get the average of the numbers in column B but only if the adjacent
cell in column A = Valid. Answer here would be 6

Thanks

John

 

[Ron Coderre]

Here are a few options:

ARRAY FORMULA, committed with CTRL+SHIFT+ENTER, instead of just ENTER
=AVERAGE(IF(A1:A5="valid",B1:B5))

ARRAY FORMULA with an activity check (to avoid DIV/0! error):
=IF(COUNTIF(A1:A5,"valid"),AVERAGE(IF(A1:A5="valid",B1:B5)),"n/a")

or...
Regular Formula:
=SUMIF(A1:A5,"valid",B1:B5)/COUNTIF(A1:A5,"valid")

Regular Formula with activity check (to avoid DIV/0! error):
=IF(COUNTIF(A1:A5,"valid"),SUMIF(A1:A5,"valid",B1:B5)/COUNTIF(A1:A5,"valid"),"n/a")


Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Marcelo]

=SUMPRODUCT(--(B12:B17=B12)*(C12:C17))/COUNTIF(B12:B17;B12)

assuming the Valid and Invalid are on B12:b17 range and the numbers on
C12:c17 range

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"JRD" escreveu: