AverageIFs returning a value error in Excel for Mac

[ghulatid]

I have a column like:

[TABLE="******* 388"]
<colgroup><col></colgroup><tbody>[TR]
[TD]closed_cost_1_0.05_0.0_0.01_wordgrams[/TD]
[/TR]
[TR]
[TD]f1[/TD]
[/TR]
[TR]
[TD]1[/TD]
[/TR]
[TR]
[TD]0[/TD]
[/TR]
[TR]
[TD]0.045454545[/TD]
[/TR]
[TR]
[TD]0[/TD]
[/TR]
[TR]
[TD]0[/TD]
[/TR]
[TR]
[TD]0[/TD]
[/TR]
[TR]
[TD]0[/TD]
[/TR]
[TR]
[TD]0[/TD]
[/TR]
[TR]
[TD]0.032258065[/TD]
[/TR]
[TR]
[TD]0[/TD]
[/TR]
[TR]
[TD]0[/TD]
[/TR]
[TR]
[TD]0.142857143[/TD]
[/TR]
[TR]
[TD]0.015267176[/TD]
[/TR]
[TR]
[TD]0[/TD]
[/TR]
[TR]
[TD]0.227272727[/TD]
[/TR]
[TR]
[TD]0.225806452[/TD]
[/TR]
[TR]
[TD]0[/TD]
[/TR]
[TR]
[TD]0.047619048[/TD]
[/TR]
[TR]
[TD]0[/TD]
[/TR]
[TR]
[TD]0[/TD]
[/TR]
[TR]
[TD]0.023529412[/TD]
[/TR]
[TR]
[TD]0.056338028[/TD]
[/TR]
[TR]
[TD]0[/TD]
[/TR]
[TR]
[TD]0[/TD]
[/TR]
[TR]
[TD]0[/TD]
[/TR]
[TR]
[TD]0[/TD]
[/TR]
[TR]
[TD]0[/TD]
[/TR]
[TR]
[TD]0.037735849[/TD]
[/TR]
[TR]
[TD]0[/TD]
[/TR]
[TR]
[TD]0[/TD]
[/TR]
[TR]
[TD]0[/TD]
[/TR]
[TR]
[TD]0.196428571[/TD]
[/TR]
[TR]
[TD]0.191780822[/TD]
[/TR]
[TR]
[TD]0[/TD]
[/TR]
[TR]
[TD]0[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]I want to average all these values if the second row is "f1" (sometimes it is "precision" or "recall").

I am trying =AVERAGEIFS(B4:B37,B2,"=f1") but it is getting a value error. Why? Also why is =AVERAGEIFS(B4:B37,B4:B37,"<>0",B2,"=f1") not working either?

Thanks.[/TD]
[/TR]
</tbody>[/TABLE]

 

[mikerickson]

In words, what are you trying to do?

If you want to average B2:B47 when B2 is f2 then =IF(B2="f2", Average(B2:B47))

 

[mikerickson]

If you have several columns and want to average only those with a particular header, you could use a formula like this

Unknown
	A	B	C	D	E	F	G
1						recall	10.625
2		recall		recall
3
4		26	0	42
5		0	0
6		0	0	0
7		0	30	42
8		19	12	41
9		0	0	0
10			0	0
11		0	0	0
12		0	0	0
Sheet1

Cell Formulas
Range	Formula
G1	=SUMPRODUCT($B$4:$D$12*($B$2:$D$2=F1))/SUMPRODUCT(--ISNUMBER($B$4:$D$12)*($B$2:$D$2=F1))