AverageIFs returning a value error in Excel for Mac

[ghulatid]

I have a column like:

closed_cost_1_0.05_0.0_0.01_wordgrams
f1
1
0
0.045454545
0
0
0
0
0
0.032258065
0
0
0.142857143
0.015267176
0
0.227272727
0.225806452
0
0.047619048
0
0
0.023529412
0.056338028
0
0
0
0
0
0.037735849
0
0
0
0.196428571
0.191780822
0
0
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.

<colgroup><col></colgroup><tbody>
</tbody>

 

[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))