AverageIfs with range criterion... can't get the right answer?

[rtilghman]

I have a spreadsheet I use to track game results for our league. I'm trying to create some metrics around our teams average goals for, against, shots, etc in cases where we won, lost, or tied. I've tried a bunch of methods to achieve this, one of which is using AverageIfs in an array. Below is an example:

=ArrayFormula(AVERAGEIFS($C$2:$C$30,$C$2:$C$30,"<>",$C$2:$C$30,">"&$F$2:$F$30))

Basically this formula says Average the values in C2:C30 (one set of scores) where the cell is not blank and the cell value is greater than the corresponding cell value in F2:F30 (the opposing team score value). Cells 14-30 are currently blank.

When I use this formula I get the value "3.17", but looking at the scores I know the average SHOULD be "3.00" (valid scores = 4, 2, 3, 3, 3).

I can't figure out why this doesn't work. I'm pretty sure the problem has to do with using a range for the criterion (always problematic), but it seems like it should work? Sadly I'm also on a Mac, and the Mac version of excel (I have excel, just doing this in google sheets) doesn't have step by step validation to see where it's failing.

Appreciate any help. Thanks!

-rt

 

[Fluff]

Hi & welcome to MrExcel.
How about

=AVERAGE(FILTER(C2:C30,(C2:C30<>"")*(C2:C30>F2:F30)))

 

[rtilghman]

That works, thanks! Still not sure why the AVERAGEIFS formula doesn't work... can *IFS just not handle cell ranges as criterion? I've searched everywhere and can't find examples where anyone seems to use cell ranges for criterion in SUMIFS, AVERAGEIFS, etc.

I have a quick follow-up questions. In my scoring sheet a team can be home (score = C column) or away (score = F column). This means I need to use the above formula twice... once to find the scores where the team won at home, and another time to find the scores where they won on the road. The issue is that if I use the formula twice (separated by a comma inside averages) the score can be inaccurate if one of the formulas = 0 (divides by two, when one might be null).

For example, here's my final formula:

=AVERAGE(IFERROR(FILTER(C2:C30,(C2:C30<>"")*(B2:B30=K19)*(C2:C30>F2:F30)),0),IFERROR(FILTER(F2:F30,(F2:F30<>"")*(E2:E30=K19)*(C2:C30<F2:F30)),0))

B = home team name
C = home team score
E = away team name
F = away team score
K = team name reference

The issue is if one of the two formulas has no matches, it will error out. I put the IFERROR 0 rule in, but obviously that won't work (NULL and "" also don't work). Basically I need this formula but I need it to just wholesale drop the result for either of the FILTER functions if there are no matches...? Not really sure how to accomplish that.

Thanks again for the help!

 

[Fluff]

You can use cell ranges for the criteria in the xxifs functions, but it won't work properly for an average.

How about

=AVERAGE(VSTACK(FILTER(C2:C30,(B2:B30=K19)*(C2:C30<>"")*(C2:C30>F2:F30),""),FILTER(F2:F30,(E2:E30=K19)*(F2:F30<>"")*(C2:C30<F2:F30),"")))

 

[rtilghman]

No dice sadly. When I enter that formula I get the following:

"#N/A" - Error = FILTER has mismatched range sizes. Expected row count: 29, column count: 1. Actual row count: 1, column count: 1

I also tried this variant, which just removed the "" at the end of each filter function (wasn't sure what their purpose was?) but didn't work either... same result.

=AVERAGE(VSTACK(FILTER(C2:C30,(B2:B30=K19)*(C2:C30<>"")*(C2:C30>F2:F30),FILTER(F2:F30,(E2:E30=K19)*(F2:F30<>"")*(C2:C30<F2:F30)))))

-rt

 

[Fluff]

Are you trying this in Excel?

 

[rtilghman]

Per the original post I wasn't (I was doing this in a google sheet for easier sharing). However, I just did and it worked. Nice.

Any idea why that formula would work in Excel but not in a google sheet? Seems weird... I was under the assumption VSTACK works fine in gsheets?

Thanks again!
rt

 

[Fluff]

I don't know enough about Sheets to know why it doesn't work.

 

[rtilghman]

Something about how vstack and filter are working together I guess... annoying it doesn't work right in sheets. I'll keep digging on it, but thanks for the help and the working solution!

 

[Fluff]

You're welcome & thanks for the feedback.