Formula apparently successful but #VALUE displayed

[Matthew Leingang]

Hello Excel Experts,

I need an Excel formula to compute an average of percentages, ignoring
the "EXC" code and zeroing out blank entries. Here is my guess:

=AVERAGE(IF(H5:AY5="EXC","",IF(ISBLANK(H5:AY5),0,H5:AY5/H$2:AY$2)))

The #VALUE error is displayed in the cell. However, if I click the
equals sign in the formula palette, I can see numbers in the dialog.
The "Number1" field is shown to have the value of an array of numbers.
There is a number next to the "Formula result=" line. So why the
error?

I'm using Excel for Mac Version 11.3.7 (070724), and spreadsheet has
been exported from Google Documents.

Thanks in advance for any help.

--Matthew Leingang

 

[David Biddulph]

Did you enter it as an array formula (Control Shift Enter, or whatever your
Mac equivalent is)?
You'll get that error if you don't.

 

[Matthew Leingang]

Oh, thanks for reminding me about that. The cell is in a list, and
even though I did enter it as an array formula, selecting "Yes" to the
dialog asking if I want to copy the formula to the other cells in the
column copies the plain (non-array, i.e., erroneous) formula.

--Matt