Problem using not equal (<>) in array formula

[fbarbie]

Hello everyone,

I have this array formula and it works fine:

{=AVERAGE(IF(raw!$V$2:raw!$V$5000="Joe Sample",raw!$AA$2:raw!$AA$5000))}

Now I want to calculate the average for everyone EXCEPT Joe Sample, so instead of an equal sign, I put <>"Joe Sample" as shown below:

{=AVERAGE(IF(raw!$V$2:raw!$V$5000<>"Joe Sample",raw!$AA$2:raw!$AA$5000))}

With this formula, I get a number that I don't know where it comes from. Its supposed to be 4.5 but I get 0.19.

Why does this not work? Thanks.

[Ron Coderre]

It sounds like you have some blank values in the list to be averaged. Those values will be calculated as part of the average. For instance, if the Non-"Joe Sample" items are: 1000, Blank, Blank, Blank, then the average will be 250, not 1000.

Is that the case?

If yes, then this formula should work:

=SUM(($V$3:$V$5000<>"Joe Sample")*($AA$3:$AA$5000<>"")*$AA$3:$AA$5000)/SUM((($V$3:$V$5000<>"Joe Sample")*($AA$3:$AA$5000<>"")))

I hope that helps.

Ron

[fbarbie]

You hit the nail right on the head.
It works brilliantly. Thank you very much.