Why does this Sumifs expression get an error?

[JenniferMurphy]

I need to calculate the average ratio of a set of numbers. This minisheet is a simple example.

In P11, I want to calculate the average ratio of the values in L/K for each value in J. The expression in O11 works, but the one in P11 does not. Can anyone tell me why? And what do I need to do to make it work. The values in J11:L16 are sorted on J, but I need the expression to work no matter how they are sorted.

Average Ratings.xlsx
	J	K	L	M	N	O	P
11	a	2	4		a	2	=sumifs(L11:L16/K11:K16,J11:J16,N11)
12	a	3	6		b	3
13	a	4	8
14	b	2	6
15	b	3	9
16	b	4	12
Test

Cell Formulas
Range	Formula
O11	O11	=SUM(L11:L13/K11:K13)/3
O12	O12	=SUM(L14:L16/K14:K16)/3

Thanks

 

[Fluff]

All of the xxxIF(s) function will allow an array for the criteria, but not the ranges which must be a valid range.

 

[JenniferMurphy]

All of the xxxIF(s) function will allow an array for the criteria, but not the ranges which must be a valid range.

Aha! "A1:A10" is a range, but "A1:A10/B1:B10" is an array, right?

Ok, but then why does it put up this error message asking me if I don't know what a formula is, rather than one saying that the first argument cannot be an array?

I swear the m$ft "developers" are clueless, lazy, or just have a really mean sense of humor.

 

[Fluff]

Ok, but then why does it put up this error message asking me if I don't know what a formula is, rather than one saying that the first argument cannot be an array?

There is no way that the developers could create an error message for each and every possible scenario, so they use a few generic messages.

"A1:A10/B1:B10" is an array, right?

That's right.