cell("prefix",reference) returns wrong results when in array formula

[jefhal]

I may be trying to do too many things in one formula, but here goes:

I need to find the average SAT scores for all students accepted at eac
of 700 colleges. In an earlier post I was shown how to do this fo
college applications based on matching the college name with eac
student cell and the results were perfect.

However, I now need to do the same thing, but with the added twist o
averaging only those SAT scores where the college name is a match AN
the college name is right justified...

Here's the array formula I have so far:

{=AVERAGE(IF(AND(CELL("prefix",StudentNames!$D$2:$R$300)="""",3>2),StudentNames!$T$2:$T$300))}

It seems to work, but all I get are zeros. When I broke it down
discovered that the "cell("prefix",StudentNames!$d$2:$r$300)" statemen
always returns a FALSE. If I use the same formula on a single cell, th
"cell" portion returns a TRUE when true and a FALSE when false.

Is there a trick to getting "cell" to work in an array formula? Is m
syntax or function order incorrect?

Thanks,

Jef

 

[Harlan Grove]

Here's the array formula I have so far:

{=AVERAGE(IF(AND(CELL("prefix",StudentNames!$D$2:$R$300)="""",3>2),
StudentNames!$T$2:$T$300))}

It seems to work, but all I get are zeros. When I broke it down I
discovered that the "cell("prefix",StudentNames!$d$2:$r$300)" statement
always returns a FALSE. If I use the same formula on a single cell, the
"cell" portion returns a TRUE when true and a FALSE when false.

....

When all else fails read the documentation. From online help for the CELL
function,

"Returns information about the formatting, location, or contents of the
upper-left cell in a reference."

CELL will not and cannot return an array result, so you can't use it to do
this in an array formula.