AVERAGEIF in Excel returns #VALUE

Question

I have some numeric data in cells A1:A10. In B2 I am trying to get average of even numbered cells from A1:A10 (A2, A4, A6..) in such a way that it should exclude the data point if the cell holds 0.

So I used AVERAGEIF formula along with IF statement to return the intended cells else 0's.

My formula in B2 is (It's an Array Formula and I have indeed pressed ctrl shift enter to get array formula)

{=AVERAGEIF(IF(MOD(ROW(A1:A10),2)=0,A1:A10,0),"<>0")}

This formula returns #VALUE! and I am not getting the reason for the same. I tried to evaluate the same and everything looks good up to the penultimate stage, however at the last calculation step it just returns #VALUE!.

Can someone help me further as to why this formula returns #VALUE!? Thanks.

Further update

I further checked that this formula works just fine in LibreOffice Calc as well as Google Sheets. In case of Google Sheets it is automatically put in an ArrayFunction formula on its own upon CSE.

Further I find that the Offline Help of Excel 2013 for AVERAGEIF does mention the fact that it accepts an Array Input. Here’s the extract from the help.

The AVERAGEIF function syntax has the following arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):

Range Required. One or more cells to average, including numbers or names, arrays, or references that contain numbers.

Criteria Required. The criteria in the form of a number, expression, cell reference, or text that defines which cells are averaged. For example, criteria can be expressed as 32, "32", ">32", "apples", or B4.

Average_range Optional. The actual set of cells to average. If omitted, range is used.

For me it’s hard to tell if this is behavior by design or some sort of missing functionality bug in MS Excel.

Answer 1

Not all functions can be used in an array formula, AVERAGIF already works similarly, probably it won't work in array formula.

You can just use this:

{=AVERAGE(IF((MOD(ROW(A1:A10),2)=0)*(A1:A10<>0),A1:A10,""))}

Modifications compared to your original formula:

• AVERAGEIF -> AVERAGE
• 0 (as third parameter of IF) -> "" (AVERAGE ignores text)
• combined the two criteria into IF:
  (MOD(ROW(A1:A10),2)=0)*(A1:A10<>0)
  • note: don't use AND and OR in array formulas as they don't work as expected, use * and + instead.