AVERAGEIF returns zero when it should return 18,589 - why?

Question

 

Interface doesn't allow me to copy and paste above into this forum correctly.

Answer 1

 

This doesn’t work:- 

=AVERAGEIF(A2:A6,"˂>*(New Office)",B2:B6) 

 - but this does:- 

=AVERAGEIF(A2:A6,"<>*(New Office)",B2:B6) 

The problem is that I was using the incorrect Inequality Operator. 

If I copy and paste the Inequality Operator from the second formula shown just above into the first (uppermost) one that first formula works OK. 

Thanks for the replies (Riny’s formula worked OK).

 

Answer 2

Please don't take any further action on this problem which I've now resolved. I'll come back and update this issue with the solution in the next few days.

Answer 3

@triptotokyo

Not sure what's happening, but your set-up works just fine for me.

Answer 4

Your formula returns 0 because of how the criteria is written. In AVERAGEIF, wildcards only work inside the quoted criteria text—your current criteria:

"<>*(New-Office)"

is interpreted literally, not as a “contains” / “does not contain” pattern.

You want to exclude any row where Column A contains the text (New Office). Use * around the search text, and no parentheses escaping needed:

=AVERAGEIF(A2:A6,"<>*New Office*",B2:B6)

This tells Excel:

*New Office* → any text containing “New Office”

<>*New Office* → anything not containing it

Your original formula returned 0 because "<>*(New-Office)" does not match any cells, so Excel thinks there are no valid entries, so the average is 0.

Here is the intended outcome

Values (excluding South (New Office) which is 0):

Region	Value
East	45678
--------	--------
East	45678
West	23789
North	-4789
Mid-West	9678

Sum = 45678 + 23789 − 4789 + 9678 = 742 + 354? Actually: 45678 + 23789 = 69467 69467 - 4789 = 64678 64678 + 9678 = 74356

Average: 74356 / 4 = **18,589**

---

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin