Average data in column B when value in column A = "x"

[gmcgough600]

Hi,

I want to average some data in a column where the data looks like this:


[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]x
[/TD]
[TD]1
[/TD]
[TD]x
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]z
[/TD]
[TD]6
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]y
[/TD]
[TD]4
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]x
[/TD]
[TD]#N/A
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]x
[/TD]
[TD]3
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]u
[/TD]
[TD]#N/A
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]z
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]v
[/TD]
[TD]12
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]v
[/TD]
[TD]8
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I want to average the data in column B but only when the value in column A is a fixed value (say "x"). I've tried this in D1 but only get #N/As:

=IF($A$1:$A$9=$C1,AVERAGE(IF(ISNUMBER(B$1:B$9),B$1:B$9,NA()),NA()),NA()) [entered as an array formula]

Any help appreciated!

 

[RoryA]

You could use:

=AVERAGEIFS(B:B,A:A,C1,B:B,"<>#N/A")